public ActionResult EditSave(QuotationRecord data)
        {
            recsys_quotation quotation;
            recsys_relate_customers customerData;

            //# validation
            if (data.id < 0)
                throw new SystemException("Invalid Entity ID");

            if (!String.IsNullOrEmpty(data.invoice) && data.dummy)
            {
                data.SaveResult.WarningMessage = "請刪除Invoice No或取消選取'有Dummy Invoice'";
                data.SaveResult.SavedSuccessfully = false;
                return Json(data);
            }

            if (!String.IsNullOrEmpty(data.minor_work))
            {
                if (data.minor_work.Length > 3)
                {
                    data.SaveResult.WarningMessage = "請修改Minor Work至3字以內";
                    data.SaveResult.SavedSuccessfully = false;
                    return Json(data);
                }
            }
            //# get old record
            var records = from q in this._db.recsys_quotation
                          join c in this._db.recsys_relate_customers on q.customer_id equals c.id into cs
                          from c in cs.DefaultIfEmpty()
                          where q.id == data.id
                          select new
                          {
                              Quotation = q,
                              CustomerData = c
                          };
            var record = records.FirstOrDefault();

            Member member = new Member("users");

            //# validation
            if (record == null)
                throw new RecordNotFoundException();

            quotation = record.Quotation;
            customerData = record.CustomerData;

            //# validation
            if (quotation == null || customerData == null)
                throw new RecordNotFoundException();
            if (!quotation.last_update.HasValue)
                throw new ImproperDataException();
            if (quotation.last_update.Value > data.FetchRecordTime)
                throw new OptimisticConcurrencyException("資料已被其他使用者更新");

            bool createNewDistrict = false;
            if (!string.IsNullOrEmpty(data.WorkingDistrictName))
            {
                recsys_district workingDistrict = this._db.recsys_district.Where(x => x.name.Trim().ToUpper().Equals(data.WorkingDistrictName.Trim().ToUpper())).SingleOrDefault();
                if (workingDistrict != null)
                {
                    data.district2 = workingDistrict.id;
                    data.group_id = workingDistrict.group_id;
                }
                else
                    createNewDistrict = true;
            }

            if (createNewDistrict)
            {
                recsys_district addNewDistrict = new recsys_district()
                {
                    code = data.WorkingDistrictName.Trim(),
                    name = data.WorkingDistrictName.Trim(),
                    group_id = this._db.recsys_group.Where(x => x.name.Equals("Unknown Group For System Migration")).SingleOrDefault().id,
                    region = 4,
                    status = 1,
                    last_update = DateTime.Now,
                    update_user_id = (int)member.infoBySession("id")
                };

                this._db.recsys_district.AddObject(addNewDistrict);
                this._db.SaveChanges();

                data.district2 = this._db.recsys_district.Where(x => x.name.Equals(data.WorkingDistrictName.Trim())).SingleOrDefault().id;
                data.group_id = this._db.recsys_district.Where(x => x.name.Equals(data.WorkingDistrictName.Trim())).SingleOrDefault().group_id;
            }

            createNewDistrict = false;
            if (!string.IsNullOrEmpty(data.MailingDistrictName))
            {
                recsys_district mailingDistrict = this._db.recsys_district.Where(x => x.name.Trim().ToUpper().Equals(data.MailingDistrictName.Trim().ToUpper())).SingleOrDefault();
                if (mailingDistrict != null)
                {
                    data.district1 = mailingDistrict.id;
                    data.group_id = mailingDistrict.group_id;
                }
                else
                    createNewDistrict = true;
            }

            if (createNewDistrict)
            {
                recsys_district addNewDistrict = new recsys_district()
                {
                    code = data.MailingDistrictName.Trim(),
                    name = data.MailingDistrictName.Trim(),
                    group_id = this._db.recsys_group.Where(x => x.name.Equals("Unknown Group For System Migration")).SingleOrDefault().id,
                    region = 4,
                    status = 1,
                    last_update = DateTime.Now,
                    update_user_id = (int)member.infoBySession("id")
                };

                this._db.recsys_district.AddObject(addNewDistrict);
                this._db.SaveChanges();

                data.district1 = this._db.recsys_district.Where(x => x.name.Equals(data.MailingDistrictName.Trim())).SingleOrDefault().id;
                data.group_id = this._db.recsys_district.Where(x => x.name.Equals(data.MailingDistrictName.Trim())).SingleOrDefault().group_id;
            }

            //# saving
            //# mapping
            quotation.billing_date = DateChecking(data.billing_date);
            quotation.confirm_date = DateChecking(data.confirm_date);
            quotation.dummy = (byte)(data.dummy ? 1 : 0);
            quotation.dummy_date = DateChecking(data.dummy_date);
            quotation.initial = data.initial;
            quotation.invoice = data.invoice;
            quotation.invoice_date = DateChecking(data.billing_date);
            quotation.minor_work = data.minor_work;
            quotation.minor_work_currency = data.minor_work_currency.HasValue ? data.minor_work_currency : 0.00;
            quotation.gp = data.gp.HasValue ? data.gp : 0.0;
            quotation.lang = data.lang;
            quotation.last_update = DateTime.Now;
            quotation.order_number = data.order_number;
            quotation.number = data.number;
            quotation.remark = data.remark2;
            quotation.status = (byte)data.status;
            quotation.subcon_estimation = data.subcon_estimation.HasValue ? data.subcon_estimation : 0.00;
            quotation.subcon_name = data.subcon_name;
            quotation.subcon_estimation2 = data.subcon_estimation2.HasValue ? data.subcon_estimation2 : 0.00;
            quotation.subcon_name2 = data.subcon_name2;
            quotation.subcon_estimation3 = data.subcon_estimation3.HasValue ? data.subcon_estimation3 : 0.00;
            quotation.subcon_name3 = data.subcon_name3;
            quotation.supplier_name = data.supplier_name;
            quotation.supplier_name2 = data.supplier_name2;
            quotation.supplier_name3 = data.supplier_name3;
            quotation.supplier_estimation = data.supplier_estimation.HasValue ? data.supplier_estimation : 0.00;
            quotation.supplier_estimation2 = data.supplier_estimation2.HasValue ? data.supplier_estimation2 : 0.00;
            quotation.supplier_estimation3 = data.supplier_estimation3.HasValue ? data.supplier_estimation3 : 0.00;
            quotation.supervision = data.supervision.HasValue ? data.supervision : 0.00;
            quotation.supplier_id = data.supplier_id.HasValue ? data.supplier_id : 0.00;
            quotation.material_estimation2 = data.material_estimation2.HasValue ? data.material_estimation2 : 0.00;
            quotation.material_estimation3 = data.material_estimation3.HasValue ? data.material_estimation3 : 0.00;
            quotation.direct_labour = data.direct_labour;
            quotation.direct_labour_cost = data.direct_labour_cost.HasValue ? data.direct_labour_cost : 0.00;
            quotation.payment_received_date = DateChecking(data.payment_received_date);
            quotation.tender_number = data.tender_number;
            quotation.update_user_id = (int)member.infoBySession("id");
            quotation.issue_date = DateChecking(data.issue_date);
            customerData.address1 = data.address1;
            customerData.address2 = data.address2;
            customerData.contact = data.contact;
            customerData.district1 = data.district1;
            customerData.district2 = data.district2;
            customerData.email = data.email;
            customerData.fax1 = data.fax1;
            customerData.fax2 = data.fax2;
            customerData.group_id = data.group_id;
            customerData.name = data.name;
            customerData.chi_name = data.chi_name;
            customerData.remark = data.remark;
            customerData.tel1 = data.tel1;
            customerData.tel2 = data.tel2;
            customerData.title = (byte)data.title;

            try
            {
                this._db.SaveChanges();
                this._db.Refresh(System.Data.Objects.RefreshMode.StoreWins, quotation);
                data.id = quotation.id;
                data.SaveResult.SavedSuccessfully = true;
            }
            catch (OptimisticConcurrencyException)
            {
                //# log down
            }

            return Json(data);
        }
        public ActionResult Edit(int id)
        {
            QuotationRecord model;

            //# validation
            if (id < 0)
                throw new SystemException("Invalid Entity ID");

            //# get old record
            var records = from q in this._db.recsys_quotation
                          join cd in this._db.recsys_relate_customers on q.customer_id equals cd.id into cds
                          from cd in cds.DefaultIfEmpty()
                          join c in this._db.recsys_customers on cd.customer_id equals c.id into cs
                          from c in cs.DefaultIfEmpty()
                          join u in this._db.recsys_users on q.update_user_id equals u.id into uss
                          from u in uss.DefaultIfEmpty()
                          join d1 in this._db.recsys_district on cd.district1 equals d1.id into d1s
                          from d1 in d1s.DefaultIfEmpty()
                          join d2 in this._db.recsys_district on cd.district2 equals d2.id into d2s
                          from d2 in d2s.DefaultIfEmpty()
                          where q.id == id
                          select
                          new
                          {
                              Quotation = q,
                              TotalEstCost = (q.subcon_estimation ?? 0) + (q.subcon_estimation2 ?? 0) + (q.subcon_estimation3 ?? 0) + (q.supplier_estimation ?? 0) + (q.supplier_estimation2 ?? 0) + (q.supplier_estimation3 ?? 0) + (q.supplier_id ?? 0) + (q.material_estimation2 ?? 0) + (q.material_estimation3 ?? 0) + (q.supervision ?? 0) + (q.direct_labour_cost ?? 0) + (q.minor_work_currency ?? 0),
                              CustomerData = cd,
                              UpdateUserName = u.name,
                              WorkingDistrictName = d2.name,
                              MailingDistrictName = d1.name,
                              MasterCustomerData = c

                          };

            var record = records.FirstOrDefault();

            //# validation
            if (record == null)
                throw new RecordNotFoundException();

            model = new QuotationRecord()
            {
                //# field mapping
                dummy = record.Quotation.dummy.ToString() == "1",
                initial = record.Quotation.initial,
                dummy_date = record.Quotation.dummy_date.HasValue ? record.Quotation.dummy_date.Value.ToString("dd-MM-yyyy") : string.Empty,
                order_number = record.Quotation.order_number,
                lang = record.Quotation.lang,
                number = record.Quotation.number,
                subcon_estimation = record.Quotation.subcon_estimation,
                subcon_name = record.Quotation.subcon_name,
                subcon_estimation2 = record.Quotation.subcon_estimation2,
                subcon_name2 = record.Quotation.subcon_name2,
                subcon_estimation3 = record.Quotation.subcon_estimation3,
                subcon_name3 = record.Quotation.subcon_name3,
                supervision = record.Quotation.supervision,
                supplier_name = record.Quotation.supplier_name,
                supplier_name2 = record.Quotation.supplier_name2,
                supplier_name3 = record.Quotation.supplier_name3,
                supplier_estimation = record.Quotation.supplier_estimation,
                supplier_estimation2 = record.Quotation.supplier_estimation2,
                supplier_estimation3 = record.Quotation.supplier_estimation3,
                supplier_id = record.Quotation.supplier_id,
                material_estimation2 = record.Quotation.material_estimation2,
                material_estimation3 = record.Quotation.material_estimation3,
                payment_received_date = record.Quotation.payment_received_date.HasValue ? record.Quotation.payment_received_date.Value.ToString("dd-MM-yyyy") : string.Empty,
                tender_number = record.Quotation.tender_number,
                direct_labour = record.Quotation.direct_labour.HasValue ? (bool)record.Quotation.direct_labour : false,
                direct_labour_cost = record.Quotation.direct_labour_cost,
                address1 = record.CustomerData.address1,
                address2 = record.CustomerData.address2,
                center = record.CustomerData.center,
                code = record.CustomerData.code,
                contact = record.CustomerData.contact,
                customer_code = record.CustomerData.customer_code,
                //customer_reference_number = record.CustomerData.reference_number,
                //get the newest reference number
                customer_reference_number = record.MasterCustomerData.reference_number,
                district1 = record.CustomerData.district1,
                district2 = record.CustomerData.district2,
                email = record.CustomerData.email,
                fax1 = record.CustomerData.fax1,
                fax2 = record.CustomerData.fax2,
                group_id = record.CustomerData.group_id,
                id = record.Quotation.id,
                invoice = record.Quotation.invoice,
                minor_work = record.Quotation.minor_work,
                minor_work_currency = record.Quotation.minor_work_currency,
                total_est_cost = record.TotalEstCost,
                gp = record.Quotation.gp,
                quotation_total_price = record.TotalEstCost + (record.Quotation.gp ?? 0),
                last_update = record.Quotation.last_update,
                last_update_user_name = record.UpdateUserName,
                MailingDistrictName = record.MailingDistrictName,
                name = record.CustomerData.name,
                chi_name = record.CustomerData.chi_name,
                prefix = record.CustomerData.prefix,
                remark = record.CustomerData.remark,
                remark2 = record.Quotation.remark,
                status = record.Quotation.status,
                tel1 = record.CustomerData.tel1,
                tel2 = record.CustomerData.tel2,
                title = record.CustomerData.title.HasValue ? record.CustomerData.title.Value : 0,
                type = record.CustomerData.type.HasValue ? record.CustomerData.type.Value : 0,
                WorkingDistrictName = record.WorkingDistrictName,
                billing_date = record.Quotation.billing_date.HasValue ? record.Quotation.billing_date.Value.ToString("dd-MM-yyyy") : string.Empty,
                invoice_date = record.Quotation.invoice_date.HasValue ? record.Quotation.invoice_date.Value.ToString("dd-MM-yyyy") : string.Empty,
                confirm_date = record.Quotation.confirm_date.HasValue ? record.Quotation.confirm_date.Value.ToString("dd-MM-yyyy") : string.Empty,
                create_date = record.Quotation.create_date,
                issue_date = record.Quotation.issue_date.HasValue ? record.Quotation.issue_date.Value.ToString("dd-MM-yyyy") : string.Empty,

                //# settings
                SaveResult = null,
                Mode = PageMode.Edit,
                FetchRecordTime = DateTime.Now,
                RelateTableName = "Maintenance",

                //# masters
                Districts = this._db.recsys_district
                                                        .Where(theDistrict => theDistrict.status == 1)
                                                        .OrderBy(theDistrict => theDistrict.name),
                Groups = this._db.recsys_group
                                                        .Where(theGroup => theGroup.status == 1)
                                                        .OrderBy(theGroup => theGroup.name),
                Titles = this._title,
                Statuses = this._status,
                Languages = this._lang
            };

            return PartialView("AddEdit", model);
        }
        public ActionResult Add(int masterCustomerID)
        {
            QuotationRecord model;

            //# validation
            if (masterCustomerID < 0)
                throw new SystemException("Invalid Entity ID");

            //# get old master customer record
            var records = from c in this._db.recsys_customers
                          join d1 in this._db.recsys_district on c.district1 equals d1.id into d1s
                          from d1 in d1s.DefaultIfEmpty()
                          join d2 in this._db.recsys_district on c.district2 equals d2.id into d2s
                          from d2 in d2s.DefaultIfEmpty()
                          where c.id == masterCustomerID
                          select new
                          {
                              masterCustomer = c,
                              MailingDistrictName = d1.name,
                              WorkingDistrictName = d2.name
                          };
            var record = records.FirstOrDefault();

            //# validation
            if (record == null)
                throw new RecordNotFoundException();

            model = new QuotationRecord()
            {
                //# customer data fields mapping
                address1 = record.masterCustomer.address1,
                address2 = record.masterCustomer.address2,
                center = record.masterCustomer.center,
                code = record.masterCustomer.code,
                contact = record.masterCustomer.contact,
                customer_code = record.masterCustomer.customer_code,
                district1 = record.masterCustomer.district1,
                district2 = record.masterCustomer.district2,
                email = record.masterCustomer.email,
                fax1 = record.masterCustomer.fax1,
                fax2 = record.masterCustomer.fax2,
                MailingDistrictName = record.MailingDistrictName,
                MasterCustomerID = masterCustomerID,
                name = record.masterCustomer.name,
                chi_name = record.masterCustomer.chi_name,
                prefix = record.masterCustomer.prefix,
                customer_reference_number = record.masterCustomer.reference_number,
                remark = record.masterCustomer.remark,
                tel1 = record.masterCustomer.tel1,
                tel2 = record.masterCustomer.tel2,
                title = record.masterCustomer.title,
                type = record.masterCustomer.type,
                WorkingDistrictName = record.WorkingDistrictName,

                //# default
                status = (int)RecordStatus.Active,
                create_date = DateTime.Now,
                issue_date = DateTime.Now.ToString("dd-MM-yyyy"),

                //# settings
                Mode = PageMode.Add,

                //# masters
                Districts = this._db.recsys_district
                                                        .Where(theDistrict => theDistrict.status == 1)
                                                        .OrderBy(theDistrict => theDistrict.name),
                Groups = this._db.recsys_group
                                                        .Where(theGroup => theGroup.status == 1)
                                                        .OrderBy(theGroup => theGroup.name),
                Titles = this._title,
                Statuses = this._status,
                Languages = this._lang

            };

            return PartialView("AddEdit", model);
        }
        public ActionResult AddSave(QuotationRecord data)
        {
            Member member;
            recsys_customers masterCustomer;
            recsys_relate_customers customerData;
            recsys_quotation quotation;

            //# validation
            if (!String.IsNullOrEmpty(data.invoice) && data.dummy)
            {
                data.SaveResult.WarningMessage = "請刪除Invoice No或取消選取'有Dummy Invoice'";
                data.SaveResult.SavedSuccessfully = false;
                return Json(data);
            }

            if (!String.IsNullOrEmpty(data.minor_work))
            {
                if (data.minor_work.Length > 3)
                {
                    data.SaveResult.WarningMessage = "請修改Minor Work至3字以內";
                    data.SaveResult.SavedSuccessfully = false;
                    return Json(data);
                }
            }

            //# validation
            if (data.MasterCustomerID < 0)
                throw new SystemException("Invalid Entity ID");

            //# get old master customer record
            masterCustomer = this._db.recsys_customers.FirstOrDefault(theCustomer => theCustomer.id == data.MasterCustomerID);

            //# validation
            if (masterCustomer == null)
                throw new RecordNotFoundException();

            member = new Member("users");

            bool createNewDistrict = false;
            if (!string.IsNullOrEmpty(data.WorkingDistrictName))
            {
                recsys_district workingDistrict = this._db.recsys_district.Where(x => x.name.Trim().ToUpper().Equals(data.WorkingDistrictName.Trim().ToUpper())).SingleOrDefault();
                if (workingDistrict != null)
                {
                    data.district2 = workingDistrict.id;
                    data.group_id = workingDistrict.group_id;
                }
                else
                    createNewDistrict = true;
            }

            if (createNewDistrict)
            {
                recsys_district addNewDistrict = new recsys_district()
                {
                    code = data.WorkingDistrictName.Trim(),
                    name = data.WorkingDistrictName.Trim(),
                    group_id = this._db.recsys_group.Where(x => x.name.Equals("Unknown Group For System Migration")).SingleOrDefault().id,
                    region = 4,
                    status = 1,
                    last_update = DateTime.Now,
                    update_user_id = (int)member.infoBySession("id")
                };

                this._db.recsys_district.AddObject(addNewDistrict);
                this._db.SaveChanges();

                data.district2 = this._db.recsys_district.Where(x => x.name.Equals(data.WorkingDistrictName.Trim())).SingleOrDefault().id;
                data.group_id = this._db.recsys_district.Where(x => x.name.Equals(data.WorkingDistrictName.Trim())).SingleOrDefault().group_id;
            }

            createNewDistrict = false;
            if (!string.IsNullOrEmpty(data.MailingDistrictName))
            {
                recsys_district mailingDistrict = this._db.recsys_district.Where(x => x.name.Trim().ToUpper().Equals(data.MailingDistrictName.Trim().ToUpper())).SingleOrDefault();
                if (mailingDistrict != null)
                {
                    data.district1 = mailingDistrict.id;
                    data.group_id = mailingDistrict.group_id;
                }
                else
                    createNewDistrict = true;
            }

            if (createNewDistrict)
            {
                recsys_district addNewDistrict = new recsys_district()
                {
                    code = data.MailingDistrictName.Trim(),
                    name = data.MailingDistrictName.Trim(),
                    group_id = this._db.recsys_group.Where(x => x.name.Equals("Unknown Group For System Migration")).SingleOrDefault().id,
                    region = 4,
                    status = 1,
                    last_update = DateTime.Now,
                    update_user_id = (int)member.infoBySession("id")
                };

                this._db.recsys_district.AddObject(addNewDistrict);
                this._db.SaveChanges();

                data.district1 = this._db.recsys_district.Where(x => x.name.Equals(data.MailingDistrictName.Trim())).SingleOrDefault().id;
                data.group_id = this._db.recsys_district.Where(x => x.name.Equals(data.MailingDistrictName.Trim())).SingleOrDefault().group_id;
            }

            //# mapping
            customerData = new recsys_relate_customers()
            {
                address1 = data.address1,
                address2 = data.address2,
                center = masterCustomer.center,
                code = masterCustomer.code,
                contact = data.contact,
                customer_code = masterCustomer.customer_code,
                customer_id = masterCustomer.id,
                district1 = data.district1,
                district2 = data.district2,
                email = data.email,
                fax1 = data.fax1,
                fax2 = data.fax2,
                group_id = data.group_id,
                manual_input_code = masterCustomer.manual_input_code,
                name = data.name,
                chi_name = data.chi_name,
                prefix = masterCustomer.prefix,
                relate_type = (int)Rec_System.Models.Customers.CustomerDataRelateType.Maintenance,
                reference_number = masterCustomer.reference_number,
                remark = data.remark,
                tel1 = data.tel1,
                tel2 = data.tel2,
                title = (byte)data.title,
                type = (byte)masterCustomer.type
            };

            this._db.recsys_relate_customers.AddObject(customerData);

            quotation = new recsys_quotation()
            {
                dummy = (byte)(data.dummy ? 1 : 0),
                dummy_date = DateChecking(data.dummy_date),
                initial = data.initial,
                lang = data.lang,
                order_number = data.order_number,
                number = data.number,
                subcon_estimation = data.subcon_estimation.HasValue ? data.subcon_estimation : 0.00,
                subcon_name = data.subcon_name,
                subcon_estimation2 = data.subcon_estimation2.HasValue ? data.subcon_estimation2 : 0.00,
                subcon_name2 = data.subcon_name2,
                subcon_estimation3 = data.subcon_estimation3.HasValue ? data.subcon_estimation3 : 0.00,
                subcon_name3 = data.subcon_name3,
                supervision = data.supervision.HasValue ? data.supervision : 0.00,
                supplier_name = data.supplier_name,
                supplier_name2 = data.supplier_name2,
                supplier_name3 = data.supplier_name3,
                supplier_estimation = data.supplier_estimation.HasValue ? data.supplier_estimation : 0.00,
                supplier_estimation2 = data.supplier_estimation2.HasValue ? data.supplier_estimation2 : 0.00,
                supplier_estimation3 = data.supplier_estimation3.HasValue ? data.supplier_estimation3 : 0.00,
                supplier_id = data.supplier_id.HasValue ? data.supplier_id : 0.00,
                material_estimation2 = data.material_estimation2.HasValue ? data.material_estimation2 : 0.00,
                material_estimation3 = data.material_estimation3.HasValue ? data.material_estimation3 : 0.00,
                payment_received_date = DateChecking(data.payment_received_date),
                tender_number = data.tender_number,
                direct_labour = data.direct_labour,
                direct_labour_cost = data.direct_labour_cost.HasValue ? data.direct_labour_cost : 0.00,
                billing_date = DateChecking(data.billing_date),
                confirm_date = DateChecking(data.confirm_date),
                create_date = DateTime.Now,
                invoice = data.invoice,
                invoice_date = DateChecking(data.billing_date),
                minor_work = data.minor_work,
                minor_work_currency = data.minor_work_currency.HasValue ? data.minor_work_currency : 0.00,
                gp = data.gp.HasValue ? data.gp : 0.00,
                last_update = DateTime.Now,
                remark = data.remark2,
                status = (byte)data.status,
                update_user_id = (int)member.infoBySession("id"),
                issue_date = DateChecking(data.issue_date)
            };

            this._db.recsys_quotation.AddObject(quotation);

            try
            {
                this._db.SaveChanges();

                this._db.Refresh(System.Data.Objects.RefreshMode.StoreWins, customerData);
                this._db.Refresh(System.Data.Objects.RefreshMode.StoreWins, quotation);

                quotation.customer_id = customerData.id;

                this._db.SaveChanges();

                data.id = quotation.id;

                data.SaveResult.SavedSuccessfully = true;
            }
            catch (OptimisticConcurrencyException)
            {
                //# log down
            }

            return Json(data);
        }