-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `party`
--

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`uparty`@`%` PROCEDURE `CustomerAccountCardCreate` (IN `SexSP` CHAR(1), IN `CustomerIdSP` VARCHAR(25), IN `NameSP` VARCHAR(20), IN `SureNameSP` VARCHAR(20), IN `FatherNameSP` VARCHAR(20), IN `MelliCodeSP` VARCHAR(16), IN `MelliSerialSP` VARCHAR(16), IN `TarikhTavallodPersianSP` DECIMAL(8), IN `TarikhTavallodLatinSP` DECIMAL(8), IN `ShenasnamehSerialSP` DECIMAL(9), IN `ShenasnamehSeriSP` CHAR(4), IN `HomeAddressSP` VARCHAR(120), IN `HomePostCodeSP` CHAR(10), IN `TelephoneFixedSP` CHAR(15), IN `TelephoneMobileSP` CHAR(15), IN `AccountIdSP` VARCHAR(26), IN `PANSP` CHAR(19), IN `AmountSP` DECIMAL(18), IN `ExpireDateSP` DATE, IN `CardTypeSP` DECIMAL(2), IN `CardStatusSP` DECIMAL(1), OUT `ReturnCodeSP` DECIMAL(5))  COMMENT 'تعریف و ثبت مشتری و برگرداندن شناسه مشتری' mainProg: BEGIN
	DECLARE tagMelli CHARACTER(1) DEFAULT('0');
	DECLARE NotFound CHARACTER(1) DEFAULT('0');
	DECLARE tagMelliSerial CHARACTER(1) DEFAULT('0');
  	DECLARE tagMobile CHARACTER(1) DEFAULT('0');
    DECLARE tagCustomerId CHARACTER(1) DEFAULT('0');
    DECLARE TagAccount CHARACTER(1) DEFAULT('0');
    DECLARE TagPAN CHARACTER(1) DEFAULT('0');
   	DECLARE LastInsertIdL1 DECIMAL(18) DEFAULT(0);
    DECLARE NowL1 date DEFAULT (Now());
	DECLARE Dummy1 int(1);
    
	DECLARE CONTINUE HANDLER FOR NOT FOUND	SET NotFound = '1';
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
    	ROLLBACK;
        SET ReturnCodeSP=100;
		SELECT ReturnCodeSP;
    END;
    
	SELECT '1' INTO tagCustomerId 	FROM CustomerInfo WHERE CustomerId = CustomerIdSP;     
	SELECT '1' INTO tagMelli 		FROM CustomerInfo WHERE MelliCode = MelliCodeSP;     
	SELECT '1' INTO tagMelliSerial 	FROM CustomerInfo WHERE MelliSerial = MelliSerialSP;     
    SELECT '1' INTO tagMobile 		FROM CustomerInfo WHERE TelephoneMobile=TelephoneMobileSP; 
    SELECT '1' INTO TagAccount 		FROM AccountQarz  WHERE AccountId=AccountIdSp;
    SELECT '1' INTO TagPAN			FROM Card		  WHERE PAN=PANSP;

IF (tagMelli = '1') THEN 
    	SET ReturnCodeSP=1000; SELECT ReturnCodeSP; LEAVE mainProg;
    ELSEIF (tagCustomerId = '1') THEN 
    	SET ReturnCodeSP=1015; SELECT ReturnCodeSP; LEAVE mainProg;
    ELSEIF (tagMobile = '1') THEN 
    	SET ReturnCodeSP=1001; SELECT ReturnCodeSP; LEAVE mainProg;
    ELSEIF (tagMelliSerial = '1') THEN 
    	SET ReturnCodeSP=1004; SELECT ReturnCodeSP; LEAVE mainProg;
    ELSEIF (tagAccount = '1') THEN 
    	SET ReturnCodeSP=1006; SELECT ReturnCodeSP; LEAVE mainProg;
    ELSEIF (tagPAN = '1') THEN 
    	SET ReturnCodeSP=1021; SELECT ReturnCodeSP; LEAVE mainProg;
	ELSE  -- NO DUPLICATE, CREATE ALL
    
   		START TRANSACTION;
    	INSERT INTO CustomerInfo (CustomerId, Name, Surename, FatherName, Sex, MelliCode, MelliSerial, TarikhTavallodPersian, TarikhTavallodLatin, ShenasnamehSerial, ShenasnamehSeri, HomeAddress, HomepostCode, TelephoneFixed, TelephoneMobile) VALUES (CustomerIdSP, NameSP, SureNameSP, FatherNameSP, SexSP, MelliCodeSP, MelliSerialSP, TarikhTavallodPersianSP, TarikhTavallodLatinSP, ShenasnamehSerialSP, ShenasnamehSeriSP, HomeAddressSP, HomePostCodeSP, TelephoneFixedSP, TelephoneMobileSP);
        
		INSERT INTO AccountQarz (AccountId,CustomerId,MelliCode,AccountStatus) VALUES (AccountIdSP,CustomerIdSP,MelliCodeSP,3);
        
		INSERT INTO CardRequest (CustomerId,MelliCode,AccountId,PAN,CardType,GiftAmount,ExpireDate,IssueStatus,IssueDate) VALUES (CustomerIdSP,MelliCodeSP,AccountIdSP,PANSP,CardTypeSP,AmountSP,ExpireDateSP,2,NowL1);
		INSERT INTO Card (PAN,CustomerId,MelliCode,AccountDefault,AccountSecond,CardType,ExpireDate) VALUES (PANSP,CustomerIdSP,MelliCodeSP,AccountIdSP,'0',CardTypeSP,ExpireDateSP);
        
    	COMMIT;
END IF;
SET ReturnCodeSP=0;
SELECT ReturnCodeSP;
END mainProg$$

CREATE DEFINER=`uparty`@`%` PROCEDURE `CustomerCreate` (IN `SexSP` CHAR(1), IN `CustomerIdSP` VARCHAR(25), IN `NameSP` VARCHAR(20), IN `SureNameSP` VARCHAR(20), IN `FatherNameSP` VARCHAR(20), IN `MelliCodeSP` VARCHAR(16), IN `MelliSerialSP` VARCHAR(16), IN `TarikhTavallodPersianSP` INT(8) UNSIGNED, IN `TarikhTavallodLatinSP` INT(8) UNSIGNED, IN `ShenasnamehSerialSP` INT(9) UNSIGNED, IN `ShenasnamehSeriSP` CHAR(4), IN `HomeAddressSP` VARCHAR(120), IN `HomePostCodeSP` CHAR(10), IN `TelephoneFixedSP` CHAR(15), IN `TelephoneMobileSP` CHAR(15), OUT `ReturnCode` INT(5) UNSIGNED)  COMMENT 'تعریف و ثبت مشتری و برگرداندن شناسه مشتری' mainProg: BEGIN
	DECLARE tagMelli CHARACTER(1) DEFAULT('0');
	DECLARE NotFound CHARACTER(1) DEFAULT('0');
	DECLARE tagMelliSerial CHARACTER(1) DEFAULT('0');
  	DECLARE tagMobile CHARACTER(1) DEFAULT('0');
    DECLARE tagCustomerId CHARACTER(1) DEFAULT('0');
    
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET NotFound = '1';
    
	SELECT '1' INTO tagCustomerId FROM CustomerInfo WHERE CustomerId = CustomerIdSP;     
	SELECT '1' INTO tagMelli FROM CustomerInfo WHERE MelliCode = MelliCodeSP;     
	SELECT '1' INTO tagMelliSerial FROM CustomerInfo WHERE MelliSerial = MelliSerialSP;     
    SELECT '1' INTO tagMobile FROM CustomerInfo WHERE TelephoneMobile=TelephoneMobileSP; 

IF (tagMelli = '1') THEN 
    	SET ReturnCode=1000; SELECT ReturnCode; LEAVE mainProg;
    ELSEIF (tagCustomerId = '1') THEN 
    	SET ReturnCode=1015; SELECT ReturnCode; LEAVE mainProg;
    ELSEIF (tagMobile = '1') THEN 
    	SET ReturnCode=1001; SELECT ReturnCode; LEAVE mainProg;
    ELSEIF (tagMelliSerial = '1') THEN 
    	SET ReturnCode=1004; SELECT ReturnCode; LEAVE mainProg;
	ELSE -- continue
    	set ReturnCode=0;
        
   	START TRANSACTION; 
        -- SELECT FLOOR(RAND()*(10000000000000000-1000000000000000+1)+1000000000000000) into CustomerId;
    	INSERT INTO CustomerInfo (CustomerId, Name, Surename, FatherName, Sex, MelliCode, MelliSerial, TarikhTavallodPersian, TarikhTavallodLatin, ShenasnamehSerial, ShenasnamehSeri, HomeAddress, HomepostCode, TelephoneFixed, TelephoneMobile) VALUES (CustomerIdSP, NameSP, SureNameSP, FatherNameSP, SexSP, MelliCodeSP, MelliSerialSP, TarikhTavallodPersianSP, TarikhTavallodLatinSP, ShenasnamehSerialSP, ShenasnamehSeriSP, HomeAddressSP, HomePostCodeSP, TelephoneFixedSP, TelephoneMobileSP);
    	COMMIT;
    END IF;
    SELECT ReturnCode;
END mainProg$$

CREATE DEFINER=`uparty`@`%` PROCEDURE `CustomerDeleteFull` (IN `CustomerIdSP` VARCHAR(25), OUT `ReturnCodeSP` INT(5) UNSIGNED)  COMMENT 'تعریف و ثبت مشتری و برگرداندن شناسه مشتری' mainProg: BEGIN
	DECLARE NotFound CHARACTER(1) DEFAULT('0');
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET ReturnCodeSP = 100;

	SET ReturnCodeSp=0;
   	START TRANSACTION; 
    	DELETE FROM AccountQarz  WHERE CustomerId=CustomerIdSP;                
    	DELETE FROM Card 		 WHERE CustomerId=CustomerIdSP;
    	DELETE FROM CardRequest  WHERE CustomerId=CustomerIdSP;        
        DELETE FROM CustomerInfo WHERE CustomerId=CustomerIdSP; 
    COMMIT;
    SELECT ReturnCodeSP;
END mainProg$$

CREATE DEFINER=`uparty`@`%` PROCEDURE `CustomerInfoGetByAccountId` (IN `AccountIdSP` VARCHAR(25), OUT `SexSP` CHAR(1), OUT `NameSP` VARCHAR(20), OUT `SureNameSP` VARCHAR(20), OUT `MelliCodeSP` CHAR(10), OUT `ReturnCodeSP` INT(5))  MODIFIES SQL DATA COMMENT 'استعلام مشتری با شماره حساب' MainProg: BEGIN
DECLARE CustomerIdL1 varchar(25);
DECLARE EXIT HANDLER FOR NOT FOUND 
	BEGIN 
    	SET ReturnCodeSP=1007;   --  شماره حساب وجود ندارد
        SELECT ReturnCodeSP;
    END;
SELECT CustomerId INTO CustomerIdL1 FROM AccountQarz WHERE AccountId = AccountIdSP; 
SELECT Sex, Name, SureName,MelliCode INTO SexSP, NameSP, SureNameSP, MelliCodeSP from CustomerInfo where CustomerId=CustomerIdL1;
SET ReturnCodeSP=0;
SELECT  SexSP,NameSP,SureNameSP,MelliCodeSP,ReturnCodeSP;
END MainProg$$

CREATE DEFINER=`uparty`@`%` PROCEDURE `CustomerInfoGetByPAN` (IN `PANSP` VARCHAR(19), OUT `ReturnCodeSP` INT(5), OUT `NameSP` VARCHAR(20), OUT `SureNameSP` VARCHAR(20), OUT `MelliCodeSP` CHAR(10), OUT `SexSP` CHAR(1))  MODIFIES SQL DATA COMMENT 'استعلام مشتری با شماره کارت' MainProg: BEGIN
DECLARE CustomerIdL1 varchar(25);
DECLARE EXIT HANDLER FOR NOT FOUND 
	BEGIN 
    	SET ReturnCodeSP=1022;
        SELECT ReturnCodeSP;
    END;
SELECT CustomerId INTO CustomerIdL1 FROM Card WHERE PAN = PANSP; 
SELECT Sex, Name, SureName,MelliCode INTO SexSP, NameSP, SureNameSP, MelliCodeSP from CustomerInfo where CustomerId=CustomerIdL1;
SET ReturnCodeSP=0;
SELECT ReturnCodeSP,NameSP,SureNameSP,MelliCodeSP,SexSP;
END MainProg$$

CREATE DEFINER=`uparty`@`%` PROCEDURE `GetCustomerByMelliSerial` (IN `MelliSerial1` CHAR(10), OUT `Name1` VARCHAR(20), OUT `Surename1` VARCHAR(20), OUT `Fathername1` VARCHAR(20), OUT `MelliCode1` CHAR(10), OUT `TelephoneFixed1` CHAR(15))   BEGIN

SELECT Name, SureName, FatherName, MelliCode, MelliSerial, TelephoneFixed INTO Name1, SureName1, FatherName1, Mellicode1, MelliSerial1, TelephoneFixed1 FROM CustomerInfo WHERE MelliSerial=MelliSerial1;

END$$

CREATE DEFINER=`uparty`@`%` PROCEDURE `GetCustomerByMobile` (OUT `Name1` VARCHAR(20), OUT `SureName1` VARCHAR(20), OUT `FatherName1` VARCHAR(20), OUT `MelliCode1` CHAR(10), OUT `MelliSerial1` CHAR(10), OUT `TarikhTavallodPersian1` INT(8), OUT `TarikhTavallodLatin1` INT(8), OUT `ShenasnamehSerial1` INT(9), OUT `ShenasnamehSeri1` CHAR(4), OUT `HomeAddress1` VARCHAR(120), OUT `HomePostCode1` BIGINT(10), OUT `TelephoneFixed1` CHAR(15), IN `TelephoneMobile1` CHAR(15), OUT `ReturnCode1` INT(4), OUT `CustomerId1` VARCHAR(20))  MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT 'استعلام مشتری با شماره ملی' BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND SET ReturnCode1 = 201;

SELECT Name, SureName, FatherName, MelliCode, MelliSerial, TarikhTavallodPersian, TarikhTavallodLatin, ShenasnamehSerial, Shenasnamehseri, HomeAddress, HomePostCode, TelephoneFixed,TelephoneMobile, CustomerId
	INTO Name1, SureName1, FatherName1, MelliCode1, MelliSerial1,  TarikhTavallodPersian1, TarikhTavallodLatin1, ShenasnamehSerial1, Shenasnamehseri1, HomeAddress1, HomePostCode1, TelephoneFixed1,TelephoneMobile1, CustomerId1 from CustomerInfo where TelephoneMobile=TelephoneMobile1;

SET ReturnCode1=0;
END$$

CREATE DEFINER=`uparty`@`%` PROCEDURE `GetCustomerInfoByCustomerId` (IN `CustomerIdSP` VARCHAR(25), OUT `SexSP` CHAR(1), OUT `NameSP` VARCHAR(20), OUT `SureNameSP` VARCHAR(20), OUT `FatherNameSP` VARCHAR(20), OUT `MelliCodeSP` CHAR(10), OUT `MelliSerialSP` CHAR(10), OUT `TarikhTavallodPersianSP` INT(8), OUT `TarikhTavallodLatinSP` INT(8), OUT `ShenasnamehSerialSP` INT(9), OUT `ShenasnamehSeriSP` CHAR(4), OUT `HomeAddressSP` VARCHAR(120), OUT `HomePostCodeSP` CHAR(10), OUT `TelephoneFixedSP` CHAR(15), OUT `TelephoneMobileSP` CHAR(15), OUT `ReturnCodeSP` INT(5))  READS SQL DATA COMMENT 'استعلام مشتری با شناسه مشتری' BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND 
	BEGIN 
    	SET ReturnCodeSP=1002;
        SELECT ReturnCodeSP;
    END;
select Sex, Name, SureName, FatherName, MelliCode, MelliSerial, TarikhTavallodPersian, TarikhTavallodLatin, ShenasnamehSerial, Shenasnamehseri, HomeAddress, HomePostCode, TelephoneFixed,TelephoneMobile
	INTO SexSP, NameSP, SureNameSP, FatherNameSP, MelliCodeSP, MelliSerialSP,  TarikhTavallodPersianSP, TarikhTavallodLatinSP, ShenasnamehSerialSP, ShenasnamehseriSP, HomeAddressSP, HomePostCodeSP, TelephoneFixedSP,TelephoneMobileSP from CustomerInfo where CustomerId=CustomerIdSP;
SET ReturnCodeSP=0;
SELECT  SexSP, NameSP, SureNameSP, FatherNameSP, MelliCodeSP, MelliSerialSP,TarikhTavallodPersianSP, TarikhTavallodLatinSP, ShenasnamehSerialSP, ShenasnamehSeriSP, HomeAddressSP, HomePostCodeSP, TelephoneFixedSP,TelephoneMobileSP, ReturnCodeSP;
END$$

CREATE DEFINER=`uparty`@`%` PROCEDURE `GetCustomerInfoByCustomerId2` (IN `CustomerIdSP` VARCHAR(25), OUT `SexSP` CHAR(1), OUT `NameSP` VARCHAR(20), OUT `SureNameSP` VARCHAR(20), OUT `FatherNameSP` VARCHAR(20), OUT `MelliCodeSP` CHAR(10), OUT `MelliSerialSP` CHAR(10), OUT `TarikhTavallodPersianSP` INT(8), OUT `TarikhTavallodLatinSP` INT(8), OUT `ShenasnamehSerialSP` INT(9), OUT `ShenasnamehSeriSP` CHAR(4), OUT `HomeAddressSP` VARCHAR(120), OUT `HomePostCodeSP` BIGINT(10), OUT `TelephoneFixedSP` CHAR(15), OUT `TelephoneMobileSP` CHAR(15), OUT `ReturnCodeSP` INT(4))  READS SQL DATA COMMENT 'استعلام مشتری با شماره ملی' BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND 
	BEGIN 
    	SET ReturnCodeSP=1002;
        SELECT ReturnCodeSP;
    END;
 
select Sex, CustomerId, Name, SureName, FatherName,MelliCode, MelliSerial, TarikhTavallodPersian, TarikhTavallodLatin, ShenasnamehSerial, Shenasnamehseri, HomeAddress, HomePostCode, TelephoneFixed,TelephoneMobile
	INTO SexSP, CustomerIdSP, NameSP, SureNameSP, FatherNameSP, MelliCodeSP, MelliSerialSP,  TarikhTavallodPersianSP, TarikhTavallodLatinSP, ShenasnamehSerialSP, ShenasnamehseriSP, HomeAddressSP, HomePostCodeSP, TelephoneFixedSP,TelephoneMobileSP from CustomerInfo where CustomerId=CustomerIdSP;
SET ReturnCodeSP=0;
SELECT  SexSP, CustomerIdSP, NameSP, SureNameSP, FatherNameSP, MelliCodeSP, MelliSerialSP,  TarikhTavallodPersianSP, TarikhTavallodLatinSP, ShenasnamehSerialSP, ShenasnamehSeriSP, HomeAddressSP, HomePostCodeSP, TelephoneFixedSP,TelephoneMobileSP, ReturnCodeSP ;
END$$

CREATE DEFINER=`uparty`@`%` PROCEDURE `GetCustomerInfoByMelli` (OUT `SexSP` CHAR(1), OUT `CustomerIdSP` VARCHAR(25), OUT `NameSP` VARCHAR(20), OUT `SureNameSP` VARCHAR(20), OUT `FatherNameSP` VARCHAR(20), IN `MelliCodeSP` CHAR(10), OUT `MelliSerialSP` CHAR(10), OUT `TarikhTavallodPersianSP` INT(8), OUT `TarikhTavallodLatinSP` INT(8), OUT `ShenasnamehSerialSP` INT(9), OUT `ShenasnamehSeriSP` CHAR(4), OUT `HomeAddressSP` VARCHAR(120), OUT `HomePostCodeSP` BIGINT(10), OUT `TelephoneFixedSP` CHAR(15), OUT `TelephoneMobileSP` CHAR(15), OUT `ReturnCodeSP` INT(4))  MODIFIES SQL DATA COMMENT 'استعلام مشتری با شماره ملی' BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND 
	BEGIN 
    	SET ReturnCodeSP=1002;
        SELECT ReturnCodeSP;
    END;
 
select Sex, CustomerId, Name, SureName, FatherName, MelliSerial, TarikhTavallodPersian, TarikhTavallodLatin, ShenasnamehSerial, Shenasnamehseri, HomeAddress, HomePostCode, TelephoneFixed,TelephoneMobile
	INTO SexSP, CustomerIdSP, NameSP, SureNameSP, FatherNameSP, MelliSerialSP,  TarikhTavallodPersianSP, TarikhTavallodLatinSP, ShenasnamehSerialSP, ShenasnamehseriSP, HomeAddressSP, HomePostCodeSP, TelephoneFixedSP,TelephoneMobileSP from CustomerInfo where MelliCode=MelliCodeSP;
SET ReturnCodeSP=0;
SELECT  SexSP, CustomerIdSP, NameSP, SureNameSP, FatherNameSP, MelliSerialSP,  TarikhTavallodPersianSP, TarikhTavallodLatinSP, ShenasnamehSerialSP, ShenasnamehSeriSP, HomeAddressSP, HomePostCodeSP, TelephoneFixedSP,TelephoneMobileSP, ReturnCodeSP ;
END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `customerinfo`
--

CREATE TABLE `customerinfo` (
  `CustomerId` varchar(25) NOT NULL COMMENT 'شناسه مشتری',
  `Name` char(20) NOT NULL DEFAULT 'نام' COMMENT 'نام کوچک',
  `SureName` char(20) NOT NULL DEFAULT 'فامیلی',
  `FatherName` char(20) NOT NULL DEFAULT 'نام پدر',
  `Sex` decimal(1,0) NOT NULL DEFAULT 1 COMMENT '1=Male or 0=Female',
  `MelliCode` char(10) NOT NULL,
  `MelliSerial` char(10) NOT NULL,
  `TarikhTavallodPersian` decimal(8,0) NOT NULL DEFAULT 13900101,
  `TarikhTavallodLatin` decimal(8,0) NOT NULL DEFAULT 20100101,
  `ShenasnamehSerial` varchar(8) DEFAULT NULL,
  `ShenasnamehSeri` varchar(4) DEFAULT NULL,
  `HomeAddress` varchar(120) NOT NULL DEFAULT 'ایران',
  `HomePostCode` char(10) NOT NULL DEFAULT '1111122222',
  `TelephoneFixed` char(15) NOT NULL DEFAULT '00982112341234',
  `TelephoneMobile` char(15) NOT NULL DEFAULT '00989121231234',
  `Updated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='مشخصات مشتری';

--
-- Dumping data for table `customerinfo`
--

INSERT INTO `customerinfo` (`CustomerId`, `Name`, `SureName`, `FatherName`, `Sex`, `MelliCode`, `MelliSerial`, `TarikhTavallodPersian`, `TarikhTavallodLatin`, `ShenasnamehSerial`, `ShenasnamehSeri`, `HomeAddress`, `HomePostCode`, `TelephoneFixed`, `TelephoneMobile`, `Updated`) VALUES
('10010', 'mor', 'mog', '-', 0, '4323267223', '1g12345678', 0, 0, '0', '0', 'تهران-مجیدیه-۲۹', '1674755614', '0', '00989123766974', '2024-11-22 03:58:17'),
('10011', 'سینا', 'مقدم', '-', 0, '0440766532', '0440766532', 0, 0, '0', '0', 'تهران', '1233567890', '0', '00989198271533', '2024-11-27 06:55:38'),
('10013', 'ناصر', 'مقدم', '-', 0, '0049733532', 'A5', 0, 0, '0', '0', 'labsiif', '1234567890', '0', '00989123873507', '2024-11-30 11:06:46');

-- --------------------------------------------------------

--
-- Table structure for table `enumcode`
--

CREATE TABLE `enumcode` (
  `EnumType` smallint(4) UNSIGNED NOT NULL,
  `EnumCode` smallint(4) NOT NULL,
  `EnumDescription` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci COMMENT='جدول کد شرحی که با نوع کد تفکیک میشود';

--
-- Dumping data for table `enumcode`
--

INSERT INTO `enumcode` (`EnumType`, `EnumCode`, `EnumDescription`) VALUES
(1, 1, 'مشتری حقیقی'),
(1, 2, 'مشتری حقوقی'),
(2, 1, 'ایرانی'),
(2, 2, 'خارجی'),
(3, 1, 'حقیقی'),
(3, 2, 'حقوقی'),
(9, 1, 'در حال افتتاح'),
(9, 2, 'افتتاح شده'),
(9, 3, 'فعال'),
(9, 4, 'مسدود\r\n'),
(9, 5, 'راکد'),
(9, 6, 'بسته شده'),
(10, 10, 'بانک مرکزی'),
(10, 11, 'بانک صنعت و معدن'),
(10, 12, 'بانک ملت'),
(10, 13, 'بانک رفاه'),
(11, 1, 'شهر'),
(11, 2, 'شهرستان'),
(11, 3, 'دهستان'),
(11, 4, 'روستا'),
(12, 1, 'شهری'),
(12, 2, 'آزاد تجاری'),
(12, 3, 'صنعتی'),
(12, 4, 'مرزی'),
(12, 5, 'روستایی'),
(13, 1, 'کارت نقدی'),
(13, 2, 'اعتباری'),
(13, 3, 'بن کارت'),
(13, 4, 'هدیه'),
(13, 5, 'کارت مجازی'),
(14, 1, 'تقاضا'),
(14, 2, 'بررسی\r\n'),
(14, 3, 'صدور'),
(14, 4, 'پست'),
(14, 5, 'تحویل'),
(14, 6, 'خطای ورودی'),
(15, 1, 'پیامک'),
(15, 2, 'چاپی'),
(15, 3, 'پیام سامانه'),
(16, 0, 'ارسال نشده'),
(16, 1, 'ارسال شده'),
(17, 1, 'اطلاعاتی و اخباری'),
(17, 2, 'اقساط سررسید گذشته'),
(17, 3, 'اقساط معوق تسهیلات'),
(17, 4, 'ورود به طبقه مشکوک الوصول'),
(18, 1, 'نداشتن حد اعتباری از اعتبار سنجی ایرانیان'),
(18, 2, 'حضور در فهرست ممنوعه بانک مرکزی'),
(19, 1, 'ریال'),
(19, 2, 'دلار'),
(19, 3, 'یورو'),
(20, 1, 'فعال'),
(20, 2, 'مسدود'),
(20, 3, 'راکد'),
(20, 4, 'معلق'),
(21, 1, 'رمز بازنشانی شد'),
(21, 2, 'مشکل ارتباط با سرور'),
(21, 3, 'کارت مسدود/راکد/معلق است'),
(22, 1, 'پوز کارت'),
(22, 2, 'خودپرداز کارت'),
(22, 3, 'اینترنت کارت'),
(22, 4, 'نرم‌افزار کارت به کارت'),
(22, 5, 'شعبه '),
(22, 6, 'پایا واریز'),
(22, 7, 'پایا برداشت'),
(22, 8, ' ساتنا واریز'),
(22, 9, 'ساتنا برداشت'),
(22, 10, 'همراه بانک واریز/برداشت/انتقالی');

-- --------------------------------------------------------

--
-- Table structure for table `enumtype`
--

CREATE TABLE `enumtype` (
  `EnumType` smallint(4) UNSIGNED NOT NULL,
  `Description` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci COMMENT='فهرست کد شرحی';

--
-- Dumping data for table `enumtype`
--

INSERT INTO `enumtype` (`EnumType`, `Description`) VALUES
(1, 'نوع مشتری - اطلاعات پایه\r\n'),
(2, 'نوع پردازش ا.ت.ا.ح'),
(3, 'ملیت'),
(4, 'شخصیت حقوقی'),
(5, 'واحد زمانی محدوده‌ها'),
(6, 'محدوده زمانی اجرا'),
(7, 'لزوم کاری'),
(8, 'روش اجرا'),
(9, 'وضعیت حساب سپرده'),
(10, 'کد بانک'),
(11, 'تقسیمات کشوری'),
(12, 'منطقه جغرافیایی'),
(13, 'نوع کارت\r\n'),
(14, 'تقاضای کارت'),
(15, 'روش ارسال اخطار و انذار تسهیلات'),
(16, 'وضعیت ارسال اخطار و  انذار تسهیلات'),
(17, 'نوع اخطار تسهیلات'),
(18, 'علت رد تقاضای وام'),
(19, 'نوع ارز'),
(20, 'وضعیت کارت'),
(21, 'عملیات رمز کارت'),
(22, 'نوع کانال یا ابزار');

-- --------------------------------------------------------

--
-- Table structure for table `paymentpurposecode`
--

CREATE TABLE `paymentpurposecode` (
  `PurposeCode` char(4) NOT NULL,
  `NameEnglish` varchar(30) NOT NULL,
  `NamePersian` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='هدف/منظور از پرداخت/تراکنش';

--
-- Dumping data for table `paymentpurposecode`
--

INSERT INTO `paymentpurposecode` (`PurposeCode`, `NameEnglish`, `NamePersian`) VALUES
('ADVA', 'Advance Payment', 'پیش پرداخت'),
('BONU', 'Bonus Payment', 'پرداخت پاداش'),
('CHAR', 'Charity Payment', 'پرداخت خیریه'),
('COST', 'Purchase of Goods', 'خرید کالا '),
('SALA', 'Salary Payment', 'پرداخت حقوق'),
('TREA', 'Treasury Payment', 'پرداخت خزانه داری');

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `ProductId` char(10) NOT NULL,
  `ProductName` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=cp1256 COLLATE=cp1256_general_ci;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`ProductId`, `ProductName`) VALUES
('1', '1234');

-- --------------------------------------------------------

--
-- Table structure for table `returncode`
--

CREATE TABLE `returncode` (
  `ReturnCode` smallint(5) UNSIGNED NOT NULL,
  `Description` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci COMMENT='شرح کدهای خطا و اشتباه';

--
-- Dumping data for table `returncode`
--

INSERT INTO `returncode` (`ReturnCode`, `Description`) VALUES
(0, 'همه چیز درست است'),
(100, 'اشکالی کلی بروز کرده است'),
(101, 'وجود ندارد'),
(102, 'کلید تکراری'),
(1000, 'این کد ملی وجود دارد'),
(1001, 'این تلفن همراه وجود دارد'),
(1002, 'این کد ملی وجود ندارد'),
(1003, 'این تلفن همراه وجود ندارد'),
(1004, 'این سریال کارت ملی وجود دارد'),
(1005, 'این سریال کارت ملی وجود ندارد'),
(1006, 'این شماره حساب وجود دارد'),
(1007, 'این شماره حساب وجود ندارد'),
(1008, 'حساب مبدا وجود ندارد'),
(1009, 'حساب مقصد وجود ندارد'),
(1010, 'موجودی حساب مبدا کافی نیست'),
(1011, 'موجودی کافی نیست'),
(1012, 'شماره حساب یا تراکنشی وجود ندارد'),
(1013, 'اشکال در ثبت تقاضای کارت'),
(1014, 'شماره ردیف تقاضای صدور کارت یافت نشد'),
(1015, 'شناسه مشتری وجود دارد'),
(1016, 'شناسه مشتری وجود ندارد'),
(1017, 'این کد پستی وجود دارد'),
(1018, 'این کد پستی وجود ندارد'),
(1019, 'شناسه وجود دارد'),
(1020, 'شناسه وجود ندارد'),
(1021, 'کارت وجود دارد'),
(1022, 'کارت وجود ندارد'),
(1023, 'کارت مبدا وجود دارد'),
(1024, 'کارت مبدا وجود ندارد'),
(1025, 'کارت مقدصد وجود دارد'),
(1026, 'کارت مقدصد وجود ندارد');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `customerinfo`
--
ALTER TABLE `customerinfo`
  ADD PRIMARY KEY (`CustomerId`),
  ADD UNIQUE KEY `MelliCode` (`MelliCode`) USING BTREE,
  ADD UNIQUE KEY `MelliSerial` (`MelliSerial`) USING BTREE,
  ADD UNIQUE KEY `TelephoneMobile` (`TelephoneMobile`),
  ADD KEY `HomePostCode` (`HomePostCode`),
  ADD KEY `TelephoneFixed` (`TelephoneFixed`),
  ADD KEY `Name` (`Name`,`SureName`,`FatherName`);

--
-- Indexes for table `enumcode`
--
ALTER TABLE `enumcode`
  ADD PRIMARY KEY (`EnumType`,`EnumCode`);

--
-- Indexes for table `enumtype`
--
ALTER TABLE `enumtype`
  ADD PRIMARY KEY (`EnumType`);

--
-- Indexes for table `paymentpurposecode`
--
ALTER TABLE `paymentpurposecode`
  ADD PRIMARY KEY (`PurposeCode`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`ProductId`);

--
-- Indexes for table `returncode`
--
ALTER TABLE `returncode`
  ADD PRIMARY KEY (`ReturnCode`);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `enumcode`
--
ALTER TABLE `enumcode`
  ADD CONSTRAINT `tc` FOREIGN KEY (`EnumType`) REFERENCES `enumtype` (`EnumType`);
COMMIT;

