private int HandleModify(string YEAR_MONTH)
{
string strMsg = "";
int status = 0;
using (TransactionScope dbTxn = new TransactionScope())
{
INVOICE_INFO DAL_INVOICE_INFO = new INVOICE_INFO(con);
//check condition
if (!DAL_INVOICE_INFO.IsAlreadyUpdatedForCSVCreate(YEAR_MONTH, out strMsg)) // IF updated_at is not already modified
{
//MODIFY the record
DAL_INVOICE_INFO.Update(YEAR_MONTH, CURRENT_DATETIME, out strMsg);
}
else
{
return(status);
}
//return message and MK value
if (String.IsNullOrEmpty(strMsg)) //success
{
dbTxn.Complete();
return(status = 1);
}
else //failed
{
return(status);
}
}
}
public Response AllocationManualCompletion(bool ALLOCATE, int SEQ_NO, string COMPANY_NO_BOX, string YEAR_MONTH, string RECEIPT_ALLOCATION_DATE, string INVOICE_ALLOCATION_DATE)
{
DateTime run_date = DateTime.Now;
string strMessage = "";
using (TransactionScope dbtnx = new TransactionScope())
{
try
{
RECEIPT_DETAILS oRCP = new RECEIPT_DETAILS(Properties.Settings.Default.MyConnection);
INVOICE_INFO oInv = new INVOICE_INFO(Properties.Settings.Default.MyConnection);
if (ALLOCATE)
{
if (string.IsNullOrEmpty(RECEIPT_ALLOCATION_DATE))
{
oRCP.UpdateAllocation(SEQ_NO, 3, run_date, out strMessage);
}
if (string.IsNullOrEmpty(strMessage) && string.IsNullOrEmpty(INVOICE_ALLOCATION_DATE))
{
oInv.UpdateAllocation(COMPANY_NO_BOX, YEAR_MONTH, 3, run_date, out strMessage);
}
}
else
{
if (!string.IsNullOrEmpty(RECEIPT_ALLOCATION_DATE))
{
oRCP.UpdateAllocation(SEQ_NO, 1, null, out strMessage);
}
if (string.IsNullOrEmpty(strMessage) && !string.IsNullOrEmpty(INVOICE_ALLOCATION_DATE))
{
oInv.UpdateAllocation(COMPANY_NO_BOX, YEAR_MONTH, 1, null, out strMessage);
}
}
if (!string.IsNullOrEmpty(strMessage))
{
response.Status = 0;
}
else
{
dbtnx.Complete();
response.Status = 1;
}
response.Message = strMessage;
return(response);
}
catch (Exception ex)
{
response.Status = 0;
response.Message = ex.Message + "\n" + ex.StackTrace;
return(response);
}
}
}
public Response do3B_BatchProcess()
{
DateTime run_date = DateTime.Now;
string strMessage = "";
using (TransactionScope dbTxn = new TransactionScope())
{
try
{
RECEIPT_DETAILS oRCP = new RECEIPT_DETAILS(Properties.Settings.Default.MyConnection);
DataTable dtReceiptDetail_Customer = oRCP.getUncompleteReceiptDetail();
for (int i = 0; i < dtReceiptDetail_Customer.Rows.Count; i++)
{
string COMPANY_NO_BOXES = (dtReceiptDetail_Customer.Rows[i]["COMPANIES"] == null ? "" : dtReceiptDetail_Customer.Rows[i]["COMPANIES"].ToString());
INVOICE_INFO oInv = new INVOICE_INFO(Properties.Settings.Default.MyConnection);
DataTable dtInvoice = oInv.getInvoiceByCustomer(COMPANY_NO_BOXES);
int SEQ_NO = 0;
int.TryParse((dtReceiptDetail_Customer.Rows[i]["SEQ_NO"] == null ? "" : dtReceiptDetail_Customer.Rows[i]["SEQ_NO"].ToString()), out SEQ_NO);
lstReserveInfo = new List <BOL_RESERVE_INFO>();
lstReceiptDetail = new List <BOL_RECEIPT_DETAILS>();
lstInvoiceInfo = new List <BOL_INVOICE_INFO>();
if (dtInvoice.Rows.Count > 0)
{
PrepareDataForUpdate(dtReceiptDetail_Customer.Rows[i], dtInvoice, run_date);
}
BOL_RECEIPT_DETAILS oRECEIPT_DETAILS = new BOL_RECEIPT_DETAILS();
oRECEIPT_DETAILS.PAYMENT_DAY = run_date;
oRECEIPT_DETAILS.SEQ_NO = SEQ_NO;
oRCP.UpdatePaymentDay(oRECEIPT_DETAILS, out strMessage);
}
dbTxn.Complete();
if (strMessage != "")
{
response.Status = 0;
}
else
{
response.Status = 1;
}
response.Message = strMessage;
return(response);
}
catch (Exception ex)
{
response.Status = 0;
response.Message = ex.Message + "\n" + ex.StackTrace;
dbTxn.Dispose();
return(response);
}
}
}
public MetaResponse CreateCSVFile(string BILLING_DATE)
{
DataTable result = new DataTable();
result.Clear();
result.Columns.Add("Check");
result.Columns.Add("Error Message");
string strMessage = "";
int status;
INVOICE_INFO DAL_INVOICE_INFO = new INVOICE_INFO(con);
DateTime yearMonth = Convert.ToDateTime(BILLING_DATE);
String YEAR_MONTH = yearMonth.ToString("yyMM");
DataTable dt = DAL_INVOICE_INFO.GetCSVList(YEAR_MONTH, out strMessage);
if (String.IsNullOrEmpty(strMessage) && dt.Rows.Count > 0)
{
#region UpdateInvoiceInfo
status = HandleModify(YEAR_MONTH);
if (status == 1)
{
response.Data = Utility.Utility_Component.DtToJSon(dt, "InvoiceInfoList");
}
else
{
DataRow dr = result.NewRow();
//dr["Count"] = count;
dr["Error Message"] = Utility.Messages.Jimugo.E000WA001; //E000WC004
result.Rows.Add(dr);
response.Data = Utility.Utility_Component.DtToJSon(result, "Return Message");
}
#endregion
}
else
{
DataRow dr = result.NewRow();
dr["Error Message"] = Utility.Messages.Jimugo.E000WC002; //E000WC002
result.Rows.Add(dr);
response.Data = Utility.Utility_Component.DtToJSon(result, "Return Message");
}
timer.Stop();
response.Meta.Duration = timer.Elapsed.TotalMilliseconds;
return(response);
}
private bool InvoiceAllocationAmountCheck(string BILLING_DATE)
{
string strMsg = "";
int count = 0;
INVOICE_INFO DAL_INVOICE_INFO = new INVOICE_INFO(con);
DateTime yearMonth = Convert.ToDateTime(BILLING_DATE);
String YEAR_MONTH = yearMonth.ToString("yyMM");
//Check Invoice Allocation
count = DAL_INVOICE_INFO.InvoiceAllocationAmountCheck(YEAR_MONTH, out strMsg);
//return message and MK value
if (String.IsNullOrEmpty(strMsg) && count > 0) //success
{
return(true);
}
else //failed
{
return(false);
}
}
private bool HandleDelete(string BILLING_DATE)
{
string strMsg = "";
//using (TransactionScope dbTxn = new TransactionScope())
//{
INVOICE_INFO DAL_INVOICE_INFO = new INVOICE_INFO(con);
DateTime yearMonth = Convert.ToDateTime(BILLING_DATE);
String YEAR_MONTH = yearMonth.ToString("yyMM");
//delete the record
DAL_INVOICE_INFO.DeleteInvoiceInfoByYearMonth(YEAR_MONTH, out strMsg);
//return message and MK value
if (String.IsNullOrEmpty(strMsg)) //success
{
return(true);
}
else //failed
{
return(false);
}
//}
}
private void PrepareDataForUpdate(DataRow dtReceiptDetail_Customer, DataTable dtBatch_Invoice, DateTime run_date)
{
decimal ALLOCATED_MONEY = 0;
decimal.TryParse((dtReceiptDetail_Customer["ALLOCATED_MONEY"] == null ? "" : dtReceiptDetail_Customer["ALLOCATED_MONEY"].ToString()), out ALLOCATED_MONEY);
decimal DEPOSIT_AMOUNT = 0;
decimal.TryParse((dtReceiptDetail_Customer["DEPOSIT_AMOUNT"] == null ? "" : dtReceiptDetail_Customer["DEPOSIT_AMOUNT"].ToString()), out DEPOSIT_AMOUNT);
decimal decAvailableAmount = DEPOSIT_AMOUNT - ALLOCATED_MONEY;
int SEQ_NO = 0;
int.TryParse((dtReceiptDetail_Customer["SEQ_NO"] == null ? "" : dtReceiptDetail_Customer["SEQ_NO"].ToString()), out SEQ_NO);
DateTime DEPOSIT_DATE;
DateTime.TryParse((dtReceiptDetail_Customer["DEPOSIT_AMOUNT"] == null ? "" : dtReceiptDetail_Customer["DEPOSIT_DATE"].ToString()), out DEPOSIT_DATE);
int ALLOCATED_QUANTITY = 0;
int.TryParse((dtReceiptDetail_Customer["ALLOCATED_QUANTITY"] == null ? "" : dtReceiptDetail_Customer["ALLOCATED_QUANTITY"].ToString()), out ALLOCATED_QUANTITY);
decimal CalculateForType = 0;
if (decAvailableAmount != 0)
{
BOL_RECEIPT_DETAILS oRECEIPT_DETAILS = new BOL_RECEIPT_DETAILS();
oRECEIPT_DETAILS.ALLOCATED_QUANTITY = ALLOCATED_QUANTITY;
oRECEIPT_DETAILS.ALLOCATED_MONEY = ALLOCATED_MONEY; //20201117_追加_ALLOCATED_MONEYを初期値でセット
#region Prepare Invoice, Reserve Info and Receipt Detail
for (int i = 0; i < dtBatch_Invoice.Rows.Count; i++)
{
//BILL PRICE
decimal BILL_PRICE = 0;
decimal.TryParse((dtBatch_Invoice.Rows[i]["BILL_PRICE"] == null ? "" : dtBatch_Invoice.Rows[i]["BILL_PRICE"].ToString()), out BILL_PRICE);
//ALLOCATATION TOTAL AMOUNT calculate
decimal ALLOCATION_TOTAL_AMOUNT = 0;
decimal.TryParse((dtBatch_Invoice.Rows[i]["ALLOCATION_TOTAL_AMOUNT"] == null ? "" : dtBatch_Invoice.Rows[i]["ALLOCATION_TOTAL_AMOUNT"].ToString()), out ALLOCATION_TOTAL_AMOUNT);
//BILL TRANSFER FEE
decimal BILL_TRANSFER_FEE = 0;
decimal.TryParse((dtBatch_Invoice.Rows[i]["BILL_TRANSFER_FEE"] == null ? "" : dtBatch_Invoice.Rows[i]["BILL_TRANSFER_FEE"].ToString()), out BILL_TRANSFER_FEE);
string strYear_Month = (dtBatch_Invoice.Rows[i]["YEAR_MONTH"] == null ? "" : dtBatch_Invoice.Rows[i]["YEAR_MONTH"].ToString());
string strCompanyNoBox = (dtBatch_Invoice.Rows[i]["COMPANY_NO_BOX"] == null ? "" : dtBatch_Invoice.Rows[i]["COMPANY_NO_BOX"].ToString());
CalculateForType = BILL_PRICE - ALLOCATION_TOTAL_AMOUNT + BILL_TRANSFER_FEE;
CalculateForType = (CalculateForType < 0 ? CalculateForType * -1 : CalculateForType);
BOL_RESERVE_INFO oRESERVE_INFO = new BOL_RESERVE_INFO();
BOL_INVOICE_INFO oInvoiceInfo = new BOL_INVOICE_INFO();
if (decAvailableAmount >= CalculateForType) //Type A
{
//Prepare Update For Invoice
oInvoiceInfo.ALLOCATION_TOTAL_AMOUNT = ALLOCATION_TOTAL_AMOUNT + CalculateForType;
oInvoiceInfo.ALLOCATED_COMPLETION_DATE = run_date;
oInvoiceInfo.YEAR_MONTH = strYear_Month;
oInvoiceInfo.COMPANY_NO_BOX = strCompanyNoBox;
oInvoiceInfo.TYPE_OF_ALLOCATION = 1;
oInvoiceInfo.STATUS_ACTUAL_DEPOSIT_YYMM = DEPOSIT_DATE.ToString("yyMM");
oInvoiceInfo.STATUS_ACTUAL_DEPOSIT_DATE = DEPOSIT_DATE.ToString("yyyy-MM-dd");
lstInvoiceInfo.Add(oInvoiceInfo);
//Prepare Insert For Reserve Info
oRESERVE_INFO.SEQ_NO = SEQ_NO;
oRESERVE_INFO.BILLING_CODE = strCompanyNoBox + strYear_Month;
oRESERVE_INFO.PAYMENT_DAY = run_date;
oRESERVE_INFO.TYPE_OF_ALLOCATION = 1;
oRESERVE_INFO.RESERVE_AMOUNT = CalculateForType;
oRESERVE_INFO.DIFF_ALLOCATION_AMOUNT = decAvailableAmount - CalculateForType;
lstReserveInfo.Add(oRESERVE_INFO);
//Prepare Update for receipt detail
oRECEIPT_DETAILS.ALLOCATED_MONEY += CalculateForType;
if (DEPOSIT_AMOUNT == oRECEIPT_DETAILS.ALLOCATED_MONEY)
{
oRECEIPT_DETAILS.ALLOCATED_COMPLETION_DATE = run_date;
}
oRECEIPT_DETAILS.SEQ_NO = SEQ_NO;
oRECEIPT_DETAILS.PAYMENT_DAY = run_date;
oRECEIPT_DETAILS.TYPE_OF_ALLOCATION = 1;
oRECEIPT_DETAILS.ALLOCATED_QUANTITY += 1;
lstReceiptDetail.Add(oRECEIPT_DETAILS);
decAvailableAmount = decAvailableAmount - CalculateForType;
}
else if (decAvailableAmount < CalculateForType) //Type B
{
//Prepare Update For Invoice Info
oInvoiceInfo.ALLOCATION_TOTAL_AMOUNT = ALLOCATION_TOTAL_AMOUNT + decAvailableAmount;
if (CalculateForType == 0)
{
oInvoiceInfo.ALLOCATED_COMPLETION_DATE = run_date;
}
oInvoiceInfo.YEAR_MONTH = strYear_Month;
oInvoiceInfo.COMPANY_NO_BOX = strCompanyNoBox;
oInvoiceInfo.TYPE_OF_ALLOCATION = 1;
oInvoiceInfo.STATUS_ACTUAL_DEPOSIT_YYMM = DEPOSIT_DATE.ToString("yyMM");
oInvoiceInfo.STATUS_ACTUAL_DEPOSIT_DATE = DEPOSIT_DATE.ToString("yyyy-MM-dd");
lstInvoiceInfo.Add(oInvoiceInfo);
//Prepare Insert For Reserve Info
oRESERVE_INFO.SEQ_NO = SEQ_NO;
oRESERVE_INFO.BILLING_CODE = strCompanyNoBox + strYear_Month;
oRESERVE_INFO.PAYMENT_DAY = run_date;
oRESERVE_INFO.TYPE_OF_ALLOCATION = 1;
oRESERVE_INFO.RESERVE_AMOUNT = decAvailableAmount;
oRESERVE_INFO.DIFF_ALLOCATION_AMOUNT = decAvailableAmount - CalculateForType;
lstReserveInfo.Add(oRESERVE_INFO);
//Prepare Update for Receipt Detail
oRECEIPT_DETAILS.ALLOCATED_COMPLETION_DATE = run_date;
oRECEIPT_DETAILS.ALLOCATED_MONEY = DEPOSIT_AMOUNT;
oRECEIPT_DETAILS.SEQ_NO = SEQ_NO;
oRECEIPT_DETAILS.PAYMENT_DAY = run_date;
oRECEIPT_DETAILS.TYPE_OF_ALLOCATION = 1;
oRECEIPT_DETAILS.ALLOCATED_QUANTITY += 1;
lstReceiptDetail.Add(oRECEIPT_DETAILS);
decAvailableAmount = decAvailableAmount - CalculateForType;
}
ALLOCATED_MONEY += oRECEIPT_DETAILS.ALLOCATED_MONEY;
if (oRECEIPT_DETAILS.ALLOCATED_COMPLETION_DATE != null)
{
break;
}
}
#endregion
RECEIPT_DETAILS oRCP = new RECEIPT_DETAILS(Properties.Settings.Default.MyConnection);
INVOICE_INFO oInv = new INVOICE_INFO(Properties.Settings.Default.MyConnection);
RESERVE_INFO oReseve = new RESERVE_INFO(Properties.Settings.Default.MyConnection);
string strMessage = "";
for (int i = 0; i < lstReceiptDetail.Count; i++)
{
oRCP.UpdateReceipt_Detail(lstReceiptDetail[i], out strMessage);
}
for (int i = 0; i < lstInvoiceInfo.Count; i++)
{
oInv.UpdateInvoice_Info(lstInvoiceInfo[i], out strMessage);
}
for (int i = 0; i < lstReserveInfo.Count; i++)
{
oReseve.insert(lstReserveInfo[i], out strMessage);
}
}
}
public Response ManualAllocation(string COMPANY_NO_BOX, string YEAR_MONTH)
{
DateTime run_date = DateTime.Now;
string strMessage = "";
using (TransactionScope dbTxn = new TransactionScope())
{
try
{
//to return matched SEQ_NO
DataTable Matched = new DataTable();
Matched.Columns.Add("Matched");
//GET uncompleted RECEIPT_DETAILS
RECEIPT_DETAILS oRCP = new RECEIPT_DETAILS(Properties.Settings.Default.MyConnection);
DataTable dtReceiptDetail_Customer = oRCP.getUncompleteReceiptDetail();
for (int i = 0; i < dtReceiptDetail_Customer.Rows.Count; i++)
{
string COMPANY_NO_BOXES = (dtReceiptDetail_Customer.Rows[i]["COMPANIES"] == null ? "" : dtReceiptDetail_Customer.Rows[i]["COMPANIES"].ToString());
INVOICE_INFO oInv = new INVOICE_INFO(Properties.Settings.Default.MyConnection);
if (COMPANY_NO_BOXES.Contains(COMPANY_NO_BOX)) //IF there is matched COMPANY_NO_BOX
{
DataTable dtInvoice = oInv.getInvoiceByCustomerManualAllocate("'" + COMPANY_NO_BOX + "'", "'" + YEAR_MONTH + "'");
if (dtInvoice.Rows.Count > 0)
{
lstReserveInfo = new List <BOL_RESERVE_INFO>();
lstReceiptDetail = new List <BOL_RECEIPT_DETAILS>();
lstInvoiceInfo = new List <BOL_INVOICE_INFO>();
PrepareDataForUpdate(dtReceiptDetail_Customer.Rows[i], dtInvoice, run_date);
DataRow dr = Matched.NewRow();
dr["Matched"] = dtReceiptDetail_Customer.Rows[i]["SEQ_NO"];
Matched.Rows.Add(dr);
}
}
}
dbTxn.Complete();//commit changes
response.Data = Utility.Utility_Component.DtToJSon(Matched, "Matched");
if (strMessage != "")
{
response.Status = 0;
}
else
{
response.Status = 1;
}
response.Message = strMessage;
return(response);
}
catch (Exception ex)
{
response.Status = 0;
response.Message = ex.Message + "\n" + ex.StackTrace;
dbTxn.Dispose();
return(response);
}
}
}
public MetaResponse getMonthlySaleAggregationList(string strDate)
{
try
{
DataTable dtList = new DataTable();
dtList.Columns.Add("TotalAmount");
dtList.Columns.Add("ReduceSales", typeof(decimal));
dtList.Columns.Add("ReducePlanDeposit", typeof(decimal));
dtList.Columns.Add("ReduceAcutualDeposit", typeof(decimal));
dtList.Columns.Add("CurrentSales", typeof(decimal));
dtList.Columns.Add("CureentPlanDeposit", typeof(decimal));
dtList.Columns.Add("CurrentAcutualDeposit", typeof(decimal));
dtList.Columns.Add("PlusSales", typeof(decimal));
dtList.Columns.Add("PlusPlanDeposit", typeof(decimal));
dtList.Columns.Add("PlusAcutualDeposit", typeof(decimal));
dtList.Rows.Add("要 元", "0", "0", "0", "0", "0", "0", "0", "0", "0");
dtList.Rows.Add("初 期 費", "0", "0", "0", "0", "0", "0", "0", "0", "0");
dtList.Rows.Add("月額利用料", "0", "0", "0", "0", "0", "0", "0", "0", "0");
dtList.Rows.Add("生産情報閲覧", "0", "0", "0", "0", "0", "0", "0", "0", "0");
dtList.Rows.Add("計", "0", "0", "0", "0", "0", "0", "0", "0", "0");
decimal totalAmount = 0;
DateTime dtDate = new DateTime();
dtDate = Convert.ToDateTime(strDate);
string strDateReduce = dtDate.AddMonths(-1).ToString("yyMM");
string strDateCurrent = dtDate.ToString("yyMM");
string strDatePlus = dtDate.AddMonths(1).ToString("yyMM");
string strMessage = "";
//int TOTAL = 0;
INVOICE_INFO DAL_INVOICE_INFO = new INVOICE_INFO(con);
DataTable dtSaleReduce = DAL_INVOICE_INFO.GetMonthlySaleAggregationSaleList(strDateReduce, out strMessage);
DataTable dtPlanDepositReduce = DAL_INVOICE_INFO.GetMonthlySaleAggregationPlanDepositList(strDateReduce, out strMessage);
DataTable dtActualDepositReduce = DAL_INVOICE_INFO.GetMonthlySaleAggregationActualDepositList(strDateReduce, out strMessage);
DataTable dtSaleCurrent = DAL_INVOICE_INFO.GetMonthlySaleAggregationSaleList(strDateCurrent, out strMessage);
DataTable dtPlanDepositCurrent = DAL_INVOICE_INFO.GetMonthlySaleAggregationPlanDepositList(strDateCurrent, out strMessage);
DataTable dtActualDepositCurrent = DAL_INVOICE_INFO.GetMonthlySaleAggregationActualDepositList(strDateCurrent, out strMessage);
DataTable dtSalePlus = DAL_INVOICE_INFO.GetMonthlySaleAggregationSaleList(strDatePlus, out strMessage);
DataTable dtPlanDepositPlus = DAL_INVOICE_INFO.GetMonthlySaleAggregationPlanDepositList(strDatePlus, out strMessage);
DataTable dtActualDepositPlus = DAL_INVOICE_INFO.GetMonthlySaleAggregationActualDepositList(strDatePlus, out strMessage);
// ReduceSales
string strMAKER_AMOUNT_TTL = "";
string strSUPPLIER_INTIAL_AMOUNT_TTL = "";
string strSUPPLIER_MONTHLY_AMOUNT_TTL = "";
string strPRODUCT_AMOUNT_TTL = "";
if (!string.IsNullOrEmpty(dtSaleReduce.Rows[0]["MAKER_AMOUNT_TTL"].ToString()))
{
strMAKER_AMOUNT_TTL = dtSaleReduce.Rows[0]["MAKER_AMOUNT_TTL"].ToString();
}
else
{
strMAKER_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtSaleReduce.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_INTIAL_AMOUNT_TTL = dtSaleReduce.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_INTIAL_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtSaleReduce.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = dtSaleReduce.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtSaleReduce.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString()))
{
strPRODUCT_AMOUNT_TTL = dtSaleReduce.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString();
}
else
{
strPRODUCT_AMOUNT_TTL = "0";
}
totalAmount = Convert.ToDecimal(strMAKER_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_INTIAL_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_MONTHLY_AMOUNT_TTL) + Convert.ToDecimal(strPRODUCT_AMOUNT_TTL);
dtList.Rows[0]["ReduceSales"] = strMAKER_AMOUNT_TTL;
dtList.Rows[1]["ReduceSales"] = strSUPPLIER_INTIAL_AMOUNT_TTL;
dtList.Rows[2]["ReduceSales"] = strSUPPLIER_MONTHLY_AMOUNT_TTL;
dtList.Rows[3]["ReduceSales"] = strPRODUCT_AMOUNT_TTL;
dtList.Rows[4]["ReduceSales"] = totalAmount;
//
// ReducePlanDeposit
strMAKER_AMOUNT_TTL = "";
strSUPPLIER_INTIAL_AMOUNT_TTL = "";
strSUPPLIER_MONTHLY_AMOUNT_TTL = "";
strPRODUCT_AMOUNT_TTL = "";
totalAmount = 0;
if (!string.IsNullOrEmpty(dtPlanDepositReduce.Rows[0]["MAKER_AMOUNT_TTL"].ToString()))
{
strMAKER_AMOUNT_TTL = dtPlanDepositReduce.Rows[0]["MAKER_AMOUNT_TTL"].ToString();
}
else
{
strMAKER_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtPlanDepositReduce.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_INTIAL_AMOUNT_TTL = dtPlanDepositReduce.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_INTIAL_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtPlanDepositReduce.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = dtPlanDepositReduce.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtPlanDepositReduce.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString()))
{
strPRODUCT_AMOUNT_TTL = dtPlanDepositReduce.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString();
}
else
{
strPRODUCT_AMOUNT_TTL = "0";
}
totalAmount = Convert.ToDecimal(strMAKER_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_INTIAL_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_MONTHLY_AMOUNT_TTL) + Convert.ToDecimal(strPRODUCT_AMOUNT_TTL);
dtList.Rows[0]["ReducePlanDeposit"] = strMAKER_AMOUNT_TTL;
dtList.Rows[1]["ReducePlanDeposit"] = strSUPPLIER_INTIAL_AMOUNT_TTL;
dtList.Rows[2]["ReducePlanDeposit"] = strSUPPLIER_MONTHLY_AMOUNT_TTL;
dtList.Rows[3]["ReducePlanDeposit"] = strPRODUCT_AMOUNT_TTL;
dtList.Rows[4]["ReducePlanDeposit"] = totalAmount;
//
// ReduceAcutualDeposit
strMAKER_AMOUNT_TTL = "";
strSUPPLIER_INTIAL_AMOUNT_TTL = "";
strSUPPLIER_MONTHLY_AMOUNT_TTL = "";
strPRODUCT_AMOUNT_TTL = "";
totalAmount = 0;
if (!string.IsNullOrEmpty(dtActualDepositReduce.Rows[0]["MAKER_AMOUNT_TTL"].ToString()))
{
strMAKER_AMOUNT_TTL = dtActualDepositReduce.Rows[0]["MAKER_AMOUNT_TTL"].ToString();
}
else
{
strMAKER_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtActualDepositReduce.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_INTIAL_AMOUNT_TTL = dtActualDepositReduce.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_INTIAL_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtActualDepositReduce.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = dtActualDepositReduce.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtActualDepositReduce.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString()))
{
strPRODUCT_AMOUNT_TTL = dtActualDepositReduce.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString();
}
else
{
strPRODUCT_AMOUNT_TTL = "0";
}
totalAmount = Convert.ToDecimal(strMAKER_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_INTIAL_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_MONTHLY_AMOUNT_TTL) + Convert.ToDecimal(strPRODUCT_AMOUNT_TTL);
dtList.Rows[0]["ReduceAcutualDeposit"] = strMAKER_AMOUNT_TTL;
dtList.Rows[1]["ReduceAcutualDeposit"] = strSUPPLIER_INTIAL_AMOUNT_TTL;
dtList.Rows[2]["ReduceAcutualDeposit"] = strSUPPLIER_MONTHLY_AMOUNT_TTL;
dtList.Rows[3]["ReduceAcutualDeposit"] = strPRODUCT_AMOUNT_TTL;
dtList.Rows[4]["ReduceAcutualDeposit"] = totalAmount;
//
//CurrentSales
strMAKER_AMOUNT_TTL = "";
strSUPPLIER_INTIAL_AMOUNT_TTL = "";
strSUPPLIER_MONTHLY_AMOUNT_TTL = "";
strPRODUCT_AMOUNT_TTL = "";
totalAmount = 0;
if (!string.IsNullOrEmpty(dtSaleCurrent.Rows[0]["MAKER_AMOUNT_TTL"].ToString()))
{
strMAKER_AMOUNT_TTL = dtSaleCurrent.Rows[0]["MAKER_AMOUNT_TTL"].ToString();
}
else
{
strMAKER_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtSaleCurrent.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_INTIAL_AMOUNT_TTL = dtSaleCurrent.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_INTIAL_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtSaleCurrent.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = dtSaleCurrent.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtSaleCurrent.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString()))
{
strPRODUCT_AMOUNT_TTL = dtSaleCurrent.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString();
}
else
{
strPRODUCT_AMOUNT_TTL = "0";
}
totalAmount = Convert.ToDecimal(strMAKER_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_INTIAL_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_MONTHLY_AMOUNT_TTL) + Convert.ToDecimal(strPRODUCT_AMOUNT_TTL);
dtList.Rows[0]["CurrentSales"] = strMAKER_AMOUNT_TTL;
dtList.Rows[1]["CurrentSales"] = strSUPPLIER_INTIAL_AMOUNT_TTL;
dtList.Rows[2]["CurrentSales"] = strSUPPLIER_MONTHLY_AMOUNT_TTL;
dtList.Rows[3]["CurrentSales"] = strPRODUCT_AMOUNT_TTL;
dtList.Rows[4]["CurrentSales"] = totalAmount;
//
//CureentPlanDeposit
strMAKER_AMOUNT_TTL = "";
strSUPPLIER_INTIAL_AMOUNT_TTL = "";
strSUPPLIER_MONTHLY_AMOUNT_TTL = "";
strPRODUCT_AMOUNT_TTL = "";
totalAmount = 0;
if (!string.IsNullOrEmpty(dtPlanDepositCurrent.Rows[0]["MAKER_AMOUNT_TTL"].ToString()))
{
strMAKER_AMOUNT_TTL = dtPlanDepositCurrent.Rows[0]["MAKER_AMOUNT_TTL"].ToString();
}
else
{
strMAKER_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtPlanDepositCurrent.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_INTIAL_AMOUNT_TTL = dtPlanDepositCurrent.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_INTIAL_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtPlanDepositCurrent.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = dtPlanDepositCurrent.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtPlanDepositCurrent.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString()))
{
strPRODUCT_AMOUNT_TTL = dtPlanDepositCurrent.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString();
}
else
{
strPRODUCT_AMOUNT_TTL = "0";
}
totalAmount = Convert.ToDecimal(strMAKER_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_INTIAL_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_MONTHLY_AMOUNT_TTL) + Convert.ToDecimal(strPRODUCT_AMOUNT_TTL);
dtList.Rows[0]["CureentPlanDeposit"] = strMAKER_AMOUNT_TTL;
dtList.Rows[1]["CureentPlanDeposit"] = strSUPPLIER_INTIAL_AMOUNT_TTL;
dtList.Rows[2]["CureentPlanDeposit"] = strSUPPLIER_MONTHLY_AMOUNT_TTL;
dtList.Rows[3]["CureentPlanDeposit"] = strPRODUCT_AMOUNT_TTL;
dtList.Rows[4]["CureentPlanDeposit"] = totalAmount;
//
//CurrentAcutualDeposit
strMAKER_AMOUNT_TTL = "";
strSUPPLIER_INTIAL_AMOUNT_TTL = "";
strSUPPLIER_MONTHLY_AMOUNT_TTL = "";
strPRODUCT_AMOUNT_TTL = "";
totalAmount = 0;
if (!string.IsNullOrEmpty(dtActualDepositCurrent.Rows[0]["MAKER_AMOUNT_TTL"].ToString()))
{
strMAKER_AMOUNT_TTL = dtActualDepositCurrent.Rows[0]["MAKER_AMOUNT_TTL"].ToString();
}
else
{
strMAKER_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtActualDepositCurrent.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_INTIAL_AMOUNT_TTL = dtActualDepositCurrent.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_INTIAL_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtActualDepositCurrent.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = dtActualDepositCurrent.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtActualDepositCurrent.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString()))
{
strPRODUCT_AMOUNT_TTL = dtActualDepositCurrent.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString();
}
else
{
strPRODUCT_AMOUNT_TTL = "0";
}
totalAmount = Convert.ToDecimal(strMAKER_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_INTIAL_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_MONTHLY_AMOUNT_TTL) + Convert.ToDecimal(strPRODUCT_AMOUNT_TTL);
dtList.Rows[0]["CurrentAcutualDeposit"] = strMAKER_AMOUNT_TTL;
dtList.Rows[1]["CurrentAcutualDeposit"] = strSUPPLIER_INTIAL_AMOUNT_TTL;
dtList.Rows[2]["CurrentAcutualDeposit"] = strSUPPLIER_MONTHLY_AMOUNT_TTL;
dtList.Rows[3]["CurrentAcutualDeposit"] = strPRODUCT_AMOUNT_TTL;
dtList.Rows[4]["CurrentAcutualDeposit"] = totalAmount;
// PlusSales
strMAKER_AMOUNT_TTL = "";
strSUPPLIER_INTIAL_AMOUNT_TTL = "";
strSUPPLIER_MONTHLY_AMOUNT_TTL = "";
strPRODUCT_AMOUNT_TTL = "";
totalAmount = 0;
if (!string.IsNullOrEmpty(dtSalePlus.Rows[0]["MAKER_AMOUNT_TTL"].ToString()))
{
strMAKER_AMOUNT_TTL = dtSalePlus.Rows[0]["MAKER_AMOUNT_TTL"].ToString();
}
else
{
strMAKER_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtSalePlus.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_INTIAL_AMOUNT_TTL = dtSalePlus.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_INTIAL_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtSalePlus.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = dtSalePlus.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtSalePlus.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString()))
{
strPRODUCT_AMOUNT_TTL = dtSalePlus.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString();
}
else
{
strPRODUCT_AMOUNT_TTL = "0";
}
totalAmount = Convert.ToDecimal(strMAKER_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_INTIAL_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_MONTHLY_AMOUNT_TTL) + Convert.ToDecimal(strPRODUCT_AMOUNT_TTL);
dtList.Rows[0]["PlusSales"] = strMAKER_AMOUNT_TTL;
dtList.Rows[1]["PlusSales"] = strSUPPLIER_INTIAL_AMOUNT_TTL;
dtList.Rows[2]["PlusSales"] = strSUPPLIER_MONTHLY_AMOUNT_TTL;
dtList.Rows[3]["PlusSales"] = strPRODUCT_AMOUNT_TTL;
dtList.Rows[4]["PlusSales"] = totalAmount;
//
// PlusPlanDeposit
strMAKER_AMOUNT_TTL = "";
strSUPPLIER_INTIAL_AMOUNT_TTL = "";
strSUPPLIER_MONTHLY_AMOUNT_TTL = "";
strPRODUCT_AMOUNT_TTL = "";
totalAmount = 0;
if (!string.IsNullOrEmpty(dtPlanDepositPlus.Rows[0]["MAKER_AMOUNT_TTL"].ToString()))
{
strMAKER_AMOUNT_TTL = dtPlanDepositPlus.Rows[0]["MAKER_AMOUNT_TTL"].ToString();
}
else
{
strMAKER_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtPlanDepositPlus.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_INTIAL_AMOUNT_TTL = dtPlanDepositPlus.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_INTIAL_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtPlanDepositPlus.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = dtPlanDepositPlus.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtPlanDepositPlus.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString()))
{
strPRODUCT_AMOUNT_TTL = dtPlanDepositPlus.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString();
}
else
{
strPRODUCT_AMOUNT_TTL = "0";
}
totalAmount = Convert.ToDecimal(strMAKER_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_INTIAL_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_MONTHLY_AMOUNT_TTL) + Convert.ToDecimal(strPRODUCT_AMOUNT_TTL);
dtList.Rows[0]["PlusPlanDeposit"] = strMAKER_AMOUNT_TTL;
dtList.Rows[1]["PlusPlanDeposit"] = strSUPPLIER_INTIAL_AMOUNT_TTL;
dtList.Rows[2]["PlusPlanDeposit"] = strSUPPLIER_MONTHLY_AMOUNT_TTL;
dtList.Rows[3]["PlusPlanDeposit"] = strPRODUCT_AMOUNT_TTL;
dtList.Rows[4]["PlusPlanDeposit"] = totalAmount;
//
//PlusAcutualDeposit
strMAKER_AMOUNT_TTL = "";
strSUPPLIER_INTIAL_AMOUNT_TTL = "";
strSUPPLIER_MONTHLY_AMOUNT_TTL = "";
strPRODUCT_AMOUNT_TTL = "";
totalAmount = 0;
if (!string.IsNullOrEmpty(dtActualDepositPlus.Rows[0]["MAKER_AMOUNT_TTL"].ToString()))
{
strMAKER_AMOUNT_TTL = dtActualDepositPlus.Rows[0]["MAKER_AMOUNT_TTL"].ToString();
}
else
{
strMAKER_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtActualDepositPlus.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_INTIAL_AMOUNT_TTL = dtActualDepositPlus.Rows[0]["SUPPLIER_INTIAL_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_INTIAL_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtActualDepositPlus.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString()))
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = dtActualDepositPlus.Rows[0]["SUPPLIER_MONTHLY_AMOUNT_TTL"].ToString();
}
else
{
strSUPPLIER_MONTHLY_AMOUNT_TTL = "0";
}
if (!string.IsNullOrEmpty(dtActualDepositPlus.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString()))
{
strPRODUCT_AMOUNT_TTL = dtActualDepositPlus.Rows[0]["PRODUCT_AMOUNT_TTL"].ToString();
}
else
{
strPRODUCT_AMOUNT_TTL = "0";
}
totalAmount = Convert.ToDecimal(strMAKER_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_INTIAL_AMOUNT_TTL) + Convert.ToDecimal(strSUPPLIER_MONTHLY_AMOUNT_TTL) + Convert.ToDecimal(strPRODUCT_AMOUNT_TTL);
dtList.Rows[0]["PlusAcutualDeposit"] = strMAKER_AMOUNT_TTL;
dtList.Rows[1]["PlusAcutualDeposit"] = strSUPPLIER_INTIAL_AMOUNT_TTL;
dtList.Rows[2]["PlusAcutualDeposit"] = strSUPPLIER_MONTHLY_AMOUNT_TTL;
dtList.Rows[3]["PlusAcutualDeposit"] = strPRODUCT_AMOUNT_TTL;
dtList.Rows[4]["PlusAcutualDeposit"] = totalAmount;
//
response.Data = Utility.Utility_Component.DtToJSon(dtList, "MonthlySaleAggregationList");
if (dtList.Rows.Count > 0)
{
response.Status = 1;
}
else
{
if (strMessage == "")
{
response.Status = 1;
response.Message = "There is no data to display.";
}
else
{
response.Status = 0;
response.Message = strMessage;
}
}
//response.Meta.Offset = OFFSET;
//response.Meta.Limit = LIMIT;
//response.Meta.Total = TOTAL;
timer.Stop();
response.Meta.Duration = timer.Elapsed.TotalMilliseconds;
return(response);
}
catch (Exception)
{
response.Status = 0;
response.Message = " Unexpected error occour.";
return(response);
}
}
public string CreateInvoiceData(string BILLING_DATE, string status)
{
string errorStatus = "";
using (TransactionScope dbTxn = new TransactionScope())
{
try
{
int OFFSET = 0;
int LIMIT = 0;
String strMessage = "";
int TOTAL;
int countForInsert = 0;
string checkGetOrCreate = "CREATE";
DateTime yearMonth = Convert.ToDateTime(BILLING_DATE);
String YEAR_MONTH = yearMonth.ToString("yyyyMM");
if (InvoiceAllocationAmountCheck(BILLING_DATE))
{
errorStatus = "3";
return(errorStatus);
}
if (status == "1")
{
//delete Existing Invoice List
bool checkDelete = HandleDelete(BILLING_DATE);
if (!checkDelete)
{
errorStatus = "0";
return(errorStatus);
}
}
//Select Invoice Data
INVOICE_INFO DAL_INVOICE_INFO = new INVOICE_INFO(con);
DataTable dt = DAL_INVOICE_INFO.GetInvoiceList(YEAR_MONTH, OFFSET, LIMIT, checkGetOrCreate, out strMessage, out TOTAL);
for (int i = 0; i < dt.Rows.Count; i++)
{
decimal Key_source_Monthly_usage_fee_REQ_SEQ = 0;
decimal Supplier_Initial_expense_REQ_SEQ = 0;
decimal Supplier_Monthly_usage_fee_REQ_SEQ = 0;
decimal Production_information_browsing_Initial_expense_REQ_SEQ = 0;
decimal View_production_information_Annual_usage_fee_REQ_SEQ = 0;
DataRow row = dt.Rows[i];
if (!string.IsNullOrEmpty(row["Key_source_Monthly_usage_fee"].ToString()))
{
Key_source_Monthly_usage_fee_REQ_SEQ = Convert.ToDecimal(row["Key_source_Monthly_usage_fee"].ToString());
}
if (!string.IsNullOrEmpty(row["Supplier_Initial_expense"].ToString()))
{
Supplier_Initial_expense_REQ_SEQ = Convert.ToDecimal(row["Supplier_Initial_expense"].ToString());
}
if (!string.IsNullOrEmpty(row["Supplier_Monthly_usage_fee"].ToString()))
{
Supplier_Monthly_usage_fee_REQ_SEQ = Convert.ToDecimal(row["Supplier_Monthly_usage_fee"].ToString());
}
if (!string.IsNullOrEmpty(row["Production_information_browsing_Initial_expense"].ToString()))
{
Production_information_browsing_Initial_expense_REQ_SEQ = Convert.ToDecimal(row["Production_information_browsing_Initial_expense"].ToString());
}
if (!string.IsNullOrEmpty(row["Viewing_production_information_Annual_usage_fee"].ToString()))
{
View_production_information_Annual_usage_fee_REQ_SEQ = Convert.ToDecimal(row["Viewing_production_information_Annual_usage_fee"].ToString());
}
BOL_INVOICE_INFO oINVOICE_INFO = new BOL_INVOICE_INFO();
#region Set value in Model
oINVOICE_INFO.COMPANY_NO_BOX = row["COMPANY_NO_BOX"].ToString();
oINVOICE_INFO.INVOICE_DATE = DateTime.Now;
oINVOICE_INFO.NCS_CUSTOMER_CODE = row["NCS_CUSTOMER_CODE"].ToString();
oINVOICE_INFO.BILL_SUPPLIER_NAME = row["BILL_COMPANY_NAME"].ToString();
oINVOICE_INFO.BILL_SUPPLIER_NAME_READING = row["COMPANY_NAME_READING"].ToString();
oINVOICE_INFO.BILL_COMPANY_NAME = row["COMPANY_NAME"].ToString();
oINVOICE_INFO.BILL_DEPARTMENT_IN_CHARGE = row["BILL_DEPARTMENT_IN_CHARGE"].ToString();
oINVOICE_INFO.BILL_CONTACT_NAME = row["BILL_CONTACT_NAME"].ToString();
oINVOICE_INFO.BILL_HONORIFIC = "様";
oINVOICE_INFO.BILL_POSTAL_CODE = row["BILL_POSTAL_CODE"].ToString();
oINVOICE_INFO.BILL_ADDRESS = row["BILL_ADDRESS"].ToString();
oINVOICE_INFO.BILL_ADDRESS_2 = row["BILL_ADDRESS_BUILDING"].ToString();
string CONTRACT_PLAN = row["CONTRACT_PLAN"].ToString();
if (CONTRACT_PLAN == "SERVER")
{
oINVOICE_INFO.PLAN_SERVER = 1;
oINVOICE_INFO.PLAN_SERVER_LIGHT = 0;
oINVOICE_INFO.PLAN_BROWSER_AUTO = 0;
oINVOICE_INFO.PLAN_BROWSER = 0;
}
if (CONTRACT_PLAN == "SERVERRIGHT")
{
oINVOICE_INFO.PLAN_SERVER = 0;
oINVOICE_INFO.PLAN_SERVER_LIGHT = 1;
oINVOICE_INFO.PLAN_BROWSER_AUTO = 0;
oINVOICE_INFO.PLAN_BROWSER = 0;
}
if (CONTRACT_PLAN == "BROWSERAUTO")
{
oINVOICE_INFO.PLAN_SERVER = 0;
oINVOICE_INFO.PLAN_SERVER_LIGHT = 0;
oINVOICE_INFO.PLAN_BROWSER_AUTO = 1;
oINVOICE_INFO.PLAN_BROWSER = 0;
}
if (CONTRACT_PLAN == "BROWSER")
{
oINVOICE_INFO.PLAN_SERVER = 0;
oINVOICE_INFO.PLAN_SERVER_LIGHT = 0;
oINVOICE_INFO.PLAN_BROWSER_AUTO = 0;
oINVOICE_INFO.PLAN_BROWSER = 1;
}
if (CONTRACT_PLAN == "PRODUCT")
{
oINVOICE_INFO.PLAN_SERVER = 0;
oINVOICE_INFO.PLAN_SERVER_LIGHT = 0;
oINVOICE_INFO.PLAN_BROWSER_AUTO = 0;
oINVOICE_INFO.PLAN_BROWSER = 0;
}
oINVOICE_INFO.PLAN_AMIGO_CAI = Utility_Component.dtColumnToInt(row["OP_AMIGO_CAI"].ToString());
oINVOICE_INFO.PLAN_AMIGO_BIZ = Utility_Component.dtColumnToInt(row["OP_AMIGO_BIZ"].ToString());
oINVOICE_INFO.PLAN_ADD_BOX_SERVER = Utility_Component.dtColumnToInt(row["OP_BOX_SERVER"].ToString());
oINVOICE_INFO.PLAN_ADD_BOX_BROWSER = Utility_Component.dtColumnToInt(row["OP_BOX_BROWSER"].ToString());
oINVOICE_INFO.PLAN_OP_FLAT = Utility_Component.dtColumnToInt(row["OP_FLAT"].ToString());
oINVOICE_INFO.PLAN_OP_SSL = Utility_Component.dtColumnToInt(row["OP_CLIENT"].ToString());
oINVOICE_INFO.PLAN_OP_BASIC_SERVICE = Utility_Component.dtColumnToInt(row["OP_BASIC_SERVICE"].ToString());
oINVOICE_INFO.PLAN_OP_ADD_SERVICE = Utility_Component.dtColumnToInt(row["OP_ADD_SERVICE"].ToString());
if (row["SOCIOS_USER_FLG"].ToString() == " ")
{
oINVOICE_INFO.PLAN_OP_SOCIOS = 0;
}
if (row["SOCIOS_USER_FLG"].ToString() == "*")
{
oINVOICE_INFO.PLAN_OP_SOCIOS = 1;
}
oINVOICE_INFO.BILL_DEPOSIT_RULES = row["BILL_DEPOSIT_RULES"].ToString();
oINVOICE_INFO.BILL_TRANSFER_FEE = Utility_Component.dtColumnToDecimal(row["BILL_TRANSFER_FEE"].ToString());
oINVOICE_INFO.BILL_EXPENSES = Utility_Component.dtColumnToDecimal(row["BILL_EXPENSES"].ToString());
DateTime date = Convert.ToDateTime(BILLING_DATE);
var lastDateOfMonth = new DateTime(date.Year, date.Month, DateTime.DaysInMonth(date.Year, date.Month)).ToString("dd");
var lastDay = new DateTime(date.Year, date.Month, DateTime.DaysInMonth(date.Year, date.Month)).DayOfWeek;
var BILL_DATE = BILLING_DATE + "/" + lastDateOfMonth;
DateTime PaymentDay = Convert.ToDateTime(BILL_DATE);
string strPaymentDay = "";
if (lastDay.ToString() == "Saturday")
{
strPaymentDay = PaymentDay.AddDays(-1).ToString();
}
if (lastDay.ToString() == "Sunday")
{
strPaymentDay = PaymentDay.AddDays(-2).ToString();
}
if (lastDay.ToString() != "Saturday" && lastDay.ToString() != "Sunday")
{
strPaymentDay = PaymentDay.ToString();
}
oINVOICE_INFO.BILL_PAYMENT_DATE = Utility_Component.dtColumnToDateTime(strPaymentDay);
oINVOICE_INFO.STATUS_PRINT = null;
oINVOICE_INFO.STATUS_MEMO = null;
oINVOICE_INFO.STATUS_SEND = null;
oINVOICE_INFO.STATUS_MAIL_DATE = null; //declare check
oINVOICE_INFO.STATUS_ACC_RECEIVABLE_DATE = null;
oINVOICE_INFO.STATUS_INVOCE_COPY_DATE = null;
oINVOICE_INFO.STATUS_ACTUAL_MDB_UPDATE = null;
oINVOICE_INFO.STATUS_ACTUAL_DEPOSIT_YYMM = null;
oINVOICE_INFO.STATUS_ACTUAL_DEPOSIT_DATE = null;
oINVOICE_INFO.STATUS_CASH_FORECAST_MM = null;
//need to change set value
string depositRuleCheck = row["BILL_DEPOSIT_RULES"].ToString();
if (depositRuleCheck == "0")
{
oINVOICE_INFO.STATUS_PLAN_DEPOSIT_YYMM = date.AddMonths(1).ToString("yyMM");
}
if (depositRuleCheck == "1")
{
oINVOICE_INFO.STATUS_PLAN_DEPOSIT_YYMM = date.ToString("yyMM");
}
if (depositRuleCheck == "2")
{
oINVOICE_INFO.STATUS_PLAN_DEPOSIT_YYMM = date.AddMonths(2).ToString("yyMM");
}
//oINVOICE_INFO.STATUS_PLAN_DEPOSIT_YYMM = row["BILL_SUPPLIER_NAME"].ToString();
oINVOICE_INFO.STATUS_PAY_NOTICE_DATE = null;
oINVOICE_INFO.TYPE_OF_ALLOCATION = 0;
oINVOICE_INFO.ALLOCATION_TOTAL_AMOUNT = 0;
oINVOICE_INFO.ALLOCATED_COMPLETION_DATE = null;
oINVOICE_INFO.DUNNING_COUNT = 0;
oINVOICE_INFO.DUNNING_DATE = null;
oINVOICE_INFO.ANSWER_DATE = null;
oINVOICE_INFO.PAYMENT_DUE_DATE = null;
oINVOICE_INFO.ANSWER_MEMO = null;
oINVOICE_INFO.SPECIAL_NOTES_1 = null;
oINVOICE_INFO.SPECIAL_NOTES_2 = null;
oINVOICE_INFO.SPECIAL_NOTES_3 = null;
oINVOICE_INFO.SPECIAL_NOTES_4 = null;
#endregion
if (Key_source_Monthly_usage_fee_REQ_SEQ > 0)
{
string TRANSACTION_TYPE = "12";
oINVOICE_INFO.TRANSACTION_TYPE = TRANSACTION_TYPE;
oINVOICE_INFO.YEAR_MONTH = "-" + yearMonth.ToString("yyMM");
oINVOICE_INFO.BILL_AMOUNT = Utility_Component.dtColumnToDecimal(row["Key_source_Monthly_usage_fee"].ToString());
oINVOICE_INFO.BILL_TAX = Utility_Component.dtColumnToDecimal(row["Key_source_Monthly_usage_fee_TAX"].ToString());
oINVOICE_INFO.BILL_PRICE = Utility_Component.dtColumnToDecimal(row["Key_source_Monthly_usage_fee_INCLUDING_TAX"].ToString());
//Insert InvoiceData
DAL_INVOICE_INFO.InsertInvoiceInfo(oINVOICE_INFO, out strMessage);
countForInsert++;
}
if (Supplier_Initial_expense_REQ_SEQ > 0)
{
string TRANSACTION_TYPE = "21";
oINVOICE_INFO.TRANSACTION_TYPE = TRANSACTION_TYPE;
oINVOICE_INFO.YEAR_MONTH = "-" + yearMonth.ToString("yyMM") + "IC";
oINVOICE_INFO.BILL_AMOUNT = Utility_Component.dtColumnToDecimal(row["Supplier_Initial_expense"].ToString());
oINVOICE_INFO.BILL_TAX = Utility_Component.dtColumnToDecimal(row["Supplier_Initial_expense_TAX"].ToString());
oINVOICE_INFO.BILL_PRICE = Utility_Component.dtColumnToDecimal(row["Supplier_Initial_expense_INCLUDING_TAX"].ToString());
//Insert InvoiceData
DAL_INVOICE_INFO.InsertInvoiceInfo(oINVOICE_INFO, out strMessage);
countForInsert++;
}
if (Supplier_Monthly_usage_fee_REQ_SEQ > 0)
{
string TRANSACTION_TYPE = "22";
oINVOICE_INFO.TRANSACTION_TYPE = TRANSACTION_TYPE;
oINVOICE_INFO.YEAR_MONTH = "-" + yearMonth.ToString("yyMM");
oINVOICE_INFO.BILL_AMOUNT = Utility_Component.dtColumnToDecimal(row["Supplier_Monthly_usage_fee"].ToString());
oINVOICE_INFO.BILL_TAX = Utility_Component.dtColumnToDecimal(row["Supplier_Monthly_usage_fee_TAX"].ToString());
oINVOICE_INFO.BILL_PRICE = Utility_Component.dtColumnToDecimal(row["Supplier_Monthly_usage_fee_INCLUDING_TAX"].ToString());
//Insert InvoiceData
DAL_INVOICE_INFO.InsertInvoiceInfo(oINVOICE_INFO, out strMessage);
countForInsert++;
}
if (Production_information_browsing_Initial_expense_REQ_SEQ > 0 || View_production_information_Annual_usage_fee_REQ_SEQ > 0)
{
string TRANSACTION_TYPE = "32";
oINVOICE_INFO.TRANSACTION_TYPE = TRANSACTION_TYPE;
oINVOICE_INFO.YEAR_MONTH = "-" + yearMonth.ToString("yyMM");
oINVOICE_INFO.BILL_AMOUNT = Utility_Component.dtColumnToDecimal(row["Production_information_browsing_Initial_expense"].ToString()) + Utility_Component.dtColumnToDecimal(row["Viewing_production_information_Annual_usage_fee"].ToString());
oINVOICE_INFO.BILL_TAX = Utility_Component.dtColumnToDecimal(row["Production_information_browsing_Initial_expense_TAX"].ToString()) + Utility_Component.dtColumnToDecimal(row["View_production_information_Annual_usage_fee_TAX"].ToString());
oINVOICE_INFO.BILL_PRICE = Utility_Component.dtColumnToDecimal(row["Production_information_browsing_Initial_expense_INCLUDING_TAX"].ToString()) + Utility_Component.dtColumnToDecimal(row["View_production_information_Annual_usage_fee_INCLUDING_TAX"].ToString());
//Insert InvoiceData
DAL_INVOICE_INFO.InsertInvoiceInfo(oINVOICE_INFO, out strMessage);
countForInsert++;
}
}
if (string.IsNullOrEmpty(strMessage) && countForInsert > 0)
{
errorStatus = "1," + countForInsert;
dbTxn.Complete();
}
else
{
errorStatus = "2";
}
}
catch (Exception ex)
{
errorStatus = "2";
}
}
return(errorStatus);
}
public MetaResponse getInvoiceList(String BILLING_DATE, int OFFSET, int LIMIT)
{
try
{
string strMessage = "";
int TOTAL = 0;
string checkGetOrCreate = "GET";
#region Declare
decimal Key_source_Monthly_usage_fee_REQ_SEQ = 0;
decimal Key_source_Monthly_usage_fee = 0;
decimal Key_source_Monthly_usage_fee_DISCOUNTED = 0;
decimal Key_source_Monthly_usage_fee_TAX = 0;
decimal Key_source_Monthly_usage_fee_INCLUDING_TAX = 0;
decimal Supplier_Initial_expense_REQ_SEQ = 0;
decimal Supplier_Initial_expense = 0;
decimal Supplier_Initial_expense_DISCOUNTED = 0;
decimal Supplier_Initial_expense_TAX = 0;
decimal Supplier_Initial_expense_INCLUDING_TAX = 0;
decimal Supplier_Monthly_usage_fee_REQ_SEQ = 0;
decimal supplier_Monthly_usage_fee = 0;
decimal Supplier_Monthly_usage_fee_DISCOUNTED = 0;
decimal Supplier_Monthly_usage_fee_TAX = 0;
decimal Supplier_Monthly_usage_fee_INCLUDING_TAX = 0;
decimal Production_information_browsing_Initial_expense_REQ_SEQ = 0;
decimal Production_information_browsing_Initial_expense = 0;
decimal Production_information_browsing_Initial_expense_DISCOUNTED = 0;
decimal Production_information_browsing_Initial_expense_TAX;
decimal Production_information_browsing_Initial_expense_INCLUDING_TAX = 0;
decimal View_production_information_Annual_usage_fee_REQ_SEQ = 0;
decimal Viewing_production_information_Annual_usage_fee = 0;
decimal View_production_information_Annual_usage_fee_DISCOUNTED = 0;
decimal View_production_information_Annual_usage_fee_TAX = 0;
decimal View_production_information_Annual_usage_fee_INCLUDING_TAX = 0;
#endregion
INVOICE_INFO DAL_INVOICE_INFO = new INVOICE_INFO(con);
DataTable dt = DAL_INVOICE_INFO.GetInvoiceList(BILLING_DATE, OFFSET, LIMIT, checkGetOrCreate, out strMessage, out TOTAL);
DataTable dtAll = DAL_INVOICE_INFO.GetInvoiceListTotal(BILLING_DATE, out strMessage);
#region Create Datatable
DataTable dtResult = new DataTable();
dtResult.Columns.Add("No");
dtResult.Columns.Add("COMPANY_NO_BOX");
dtResult.Columns.Add("COMPANY_NAME");
dtResult.Columns.Add("KEY_SOURCE_MONTHLY_USAGE_FEE", typeof(decimal));
dtResult.Columns.Add("SUPPLIER_INITIAL_EXPENSE", typeof(decimal));
dtResult.Columns.Add("SUPPLIER_MONTHLY_USAGE_FEE", typeof(decimal));
dtResult.Columns.Add("BROWSING_INITIAL_EXPENSE", typeof(decimal));
dtResult.Columns.Add("YEARLY_USAGE_FEE", typeof(decimal));
dtResult.Columns.Add("POSTAL_MAIL");
dtResult.Columns.Add("WEB");
dtResult.Columns.Add("Email");
dtResult.Columns.Add("CREDIT_CARD");
dtResult.Columns.Add("OTHER");
dtResult.Columns.Add("Key_source_Monthly_usage_fee_REQ_SEQ");
dtResult.Columns.Add("Supplier_Initial_expense_REQ_SEQ");
dtResult.Columns.Add("Supplier_Monthly_usage_fee_REQ_SEQ");
dtResult.Columns.Add("Production_information_browsing_Initial_expense_REQ_SEQ");
dtResult.Columns.Add("View_production_information_Annual_usage_fee_REQ_SEQ");
#endregion
#region Create Datatable ForTotal
DataTable dtTotal = new DataTable();
dtTotal.Columns.Add("KEY_SOURCE_MONTHLY_USAGE_FEE", typeof(decimal));
dtTotal.Columns.Add("SUPPLIER_INITIAL_EXPENSE", typeof(decimal));
dtTotal.Columns.Add("SUPPLIER_MONTHLY_USAGE_FEE", typeof(decimal));
dtTotal.Columns.Add("BROWSING_INITIAL_EXPENSE", typeof(decimal));
dtTotal.Columns.Add("YEARLY_USAGE_FEE", typeof(decimal));
dtTotal.Columns.Add("POSTAL_MAIL");
dtTotal.Columns.Add("WEB");
dtTotal.Columns.Add("Email");
dtTotal.Columns.Add("CREDIT_CARD");
dtTotal.Columns.Add("OTHER");
#endregion
foreach (DataRow row in dt.Rows)
{
#region SetValue To DataTable
DataRow newRow = dtResult.NewRow();
newRow["No"] = row["No"];
newRow["COMPANY_NO_BOX"] = row["COMPANY_NO_BOX"];
newRow["COMPANY_NAME"] = row["COMPANY_NAME"];
Key_source_Monthly_usage_fee_REQ_SEQ = NullOrEmpty(row["Key_source_Monthly_usage_fee_REQ_SEQ"].ToString());
Key_source_Monthly_usage_fee = NullOrEmpty(row["Key_source_Monthly_usage_fee"].ToString());
Key_source_Monthly_usage_fee_DISCOUNTED = NullOrEmpty(row["Key_source_Monthly_usage_fee_DISCOUNTED"].ToString());
Key_source_Monthly_usage_fee_TAX = NullOrEmpty(row["Key_source_Monthly_usage_fee_TAX"].ToString());
Key_source_Monthly_usage_fee_INCLUDING_TAX = NullOrEmpty(row["Key_source_Monthly_usage_fee_INCLUDING_TAX"].ToString());
Supplier_Initial_expense_REQ_SEQ = NullOrEmpty(row["Supplier_Initial_expense_REQ_SEQ"].ToString());
Supplier_Initial_expense = NullOrEmpty(row["Supplier_Initial_expense"].ToString());
Supplier_Initial_expense_DISCOUNTED = NullOrEmpty(row["Supplier_Initial_expense_DISCOUNTED"].ToString());
Supplier_Initial_expense_TAX = NullOrEmpty(row["Supplier_Initial_expense_TAX"].ToString());
Supplier_Initial_expense_INCLUDING_TAX = NullOrEmpty(row["Supplier_Initial_expense_INCLUDING_TAX"].ToString());
Supplier_Monthly_usage_fee_REQ_SEQ = NullOrEmpty(row["Supplier_Monthly_usage_fee_REQ_SEQ"].ToString());
supplier_Monthly_usage_fee = NullOrEmpty(row["Supplier_Monthly_usage_fee"].ToString());
Supplier_Monthly_usage_fee_DISCOUNTED = NullOrEmpty(row["Supplier_Monthly_usage_fee_DISCOUNTED"].ToString());
Supplier_Monthly_usage_fee_TAX = NullOrEmpty(row["Supplier_Monthly_usage_fee_TAX"].ToString());
Supplier_Monthly_usage_fee_INCLUDING_TAX = NullOrEmpty(row["Supplier_Monthly_usage_fee_INCLUDING_TAX"].ToString());
Production_information_browsing_Initial_expense_REQ_SEQ = NullOrEmpty(row["Production_information_browsing_Initial_expense_REQ_SEQ"].ToString());
Production_information_browsing_Initial_expense = NullOrEmpty(row["Production_information_browsing_Initial_expense"].ToString());
Production_information_browsing_Initial_expense_DISCOUNTED = NullOrEmpty(row["Production_information_browsing_Initial_expense_DISCOUNTED"].ToString());
Production_information_browsing_Initial_expense_TAX = NullOrEmpty(row["Production_information_browsing_Initial_expense_TAX"].ToString());
Production_information_browsing_Initial_expense_INCLUDING_TAX = NullOrEmpty(row["Production_information_browsing_Initial_expense_INCLUDING_TAX"].ToString());
View_production_information_Annual_usage_fee_REQ_SEQ = NullOrEmpty(row["View_production_information_Annual_usage_fee_REQ_SEQ"].ToString());
Viewing_production_information_Annual_usage_fee = NullOrEmpty(row["Viewing_production_information_Annual_usage_fee"].ToString());
View_production_information_Annual_usage_fee_DISCOUNTED = NullOrEmpty(row["View_production_information_Annual_usage_fee_DISCOUNTED"].ToString());
View_production_information_Annual_usage_fee_TAX = NullOrEmpty(row["View_production_information_Annual_usage_fee_TAX"].ToString());
View_production_information_Annual_usage_fee_INCLUDING_TAX = NullOrEmpty(row["View_production_information_Annual_usage_fee_INCLUDING_TAX"].ToString());
decimal usageFee = Key_source_Monthly_usage_fee_REQ_SEQ + Key_source_Monthly_usage_fee + Key_source_Monthly_usage_fee_DISCOUNTED + Key_source_Monthly_usage_fee_TAX + Key_source_Monthly_usage_fee_INCLUDING_TAX;
newRow["KEY_SOURCE_MONTHLY_USAGE_FEE"] = usageFee;
decimal supplierInitialExpense = Supplier_Initial_expense_REQ_SEQ + Supplier_Initial_expense + Supplier_Initial_expense_DISCOUNTED + Supplier_Initial_expense_TAX + Supplier_Initial_expense_INCLUDING_TAX;
newRow["SUPPLIER_INITIAL_EXPENSE"] = supplierInitialExpense;
decimal supplierMonthlyUsageFee = Supplier_Monthly_usage_fee_REQ_SEQ + supplier_Monthly_usage_fee + Supplier_Monthly_usage_fee_DISCOUNTED + Supplier_Monthly_usage_fee_TAX + Supplier_Monthly_usage_fee_INCLUDING_TAX;
newRow["SUPPLIER_MONTHLY_USAGE_FEE"] = supplierMonthlyUsageFee;
decimal browsingInitialExpense = Production_information_browsing_Initial_expense_REQ_SEQ + Production_information_browsing_Initial_expense + Production_information_browsing_Initial_expense_DISCOUNTED + Production_information_browsing_Initial_expense_TAX + Production_information_browsing_Initial_expense_INCLUDING_TAX;
newRow["BROWSING_INITIAL_EXPENSE"] = browsingInitialExpense;
decimal yearlyUsageFee = View_production_information_Annual_usage_fee_REQ_SEQ + Viewing_production_information_Annual_usage_fee + View_production_information_Annual_usage_fee_DISCOUNTED + View_production_information_Annual_usage_fee_TAX + View_production_information_Annual_usage_fee_INCLUDING_TAX;
newRow["YEARLY_USAGE_FEE"] = yearlyUsageFee;
//newRow["INVOICE_METHOD"] = row["BILL_METHOD"];
string invoiceMethod = row["BILL_METHOD"].ToString();
if (invoiceMethod.Length == 5)
{
if (invoiceMethod.Substring(0, 1) == "1")
{
newRow["POSTAL_MAIL"] = "●";
}
if (invoiceMethod.Substring(1, 1) == "1")
{
newRow["WEB"] = "●";
}
if (invoiceMethod.Substring(2, 1) == "1")
{
newRow["Email"] = "●";
}
if (invoiceMethod.Substring(3, 1) == "1")
{
newRow["CREDIT_CARD"] = "●";
}
if (invoiceMethod.Substring(4, 1) == "1")
{
newRow["OTHER"] = "●";
}
}
newRow["Key_source_Monthly_usage_fee_REQ_SEQ"] = row["Key_source_Monthly_usage_fee_REQ_SEQ"];
newRow["Supplier_Initial_expense_REQ_SEQ"] = row["Supplier_Initial_expense_REQ_SEQ"];
newRow["Supplier_Monthly_usage_fee_REQ_SEQ"] = row["Supplier_Monthly_usage_fee_REQ_SEQ"];
newRow["Production_information_browsing_Initial_expense_REQ_SEQ"] = row["Production_information_browsing_Initial_expense_REQ_SEQ"];
newRow["View_production_information_Annual_usage_fee_REQ_SEQ"] = row["View_production_information_Annual_usage_fee_REQ_SEQ"];
dtResult.Rows.Add(newRow);
#endregion
}
foreach (DataRow row in dtAll.Rows)
{
#region Declare
Key_source_Monthly_usage_fee_REQ_SEQ = 0;
Key_source_Monthly_usage_fee = 0;
Key_source_Monthly_usage_fee_DISCOUNTED = 0;
Key_source_Monthly_usage_fee_TAX = 0;
Key_source_Monthly_usage_fee_INCLUDING_TAX = 0;
Supplier_Initial_expense_REQ_SEQ = 0;
Supplier_Initial_expense = 0;
Supplier_Initial_expense_DISCOUNTED = 0;
Supplier_Initial_expense_TAX = 0;
Supplier_Initial_expense_INCLUDING_TAX = 0;
Supplier_Monthly_usage_fee_REQ_SEQ = 0;
supplier_Monthly_usage_fee = 0;
Supplier_Monthly_usage_fee_DISCOUNTED = 0;
Supplier_Monthly_usage_fee_TAX = 0;
Supplier_Monthly_usage_fee_INCLUDING_TAX = 0;
Production_information_browsing_Initial_expense_REQ_SEQ = 0;
Production_information_browsing_Initial_expense = 0;
Production_information_browsing_Initial_expense_DISCOUNTED = 0;
Production_information_browsing_Initial_expense_TAX = 0;
Production_information_browsing_Initial_expense_INCLUDING_TAX = 0;
View_production_information_Annual_usage_fee_REQ_SEQ = 0;
Viewing_production_information_Annual_usage_fee = 0;
View_production_information_Annual_usage_fee_DISCOUNTED = 0;
View_production_information_Annual_usage_fee_TAX = 0;
View_production_information_Annual_usage_fee_INCLUDING_TAX = 0;
#endregion
#region SetValue to DataTable
DataRow newRow = dtTotal.NewRow();
Key_source_Monthly_usage_fee_REQ_SEQ = NullOrEmpty(row["Key_source_Monthly_usage_fee_REQ_SEQ"].ToString());
Key_source_Monthly_usage_fee = NullOrEmpty(row["Key_source_Monthly_usage_fee"].ToString());
Key_source_Monthly_usage_fee_DISCOUNTED = NullOrEmpty(row["Key_source_Monthly_usage_fee_DISCOUNTED"].ToString());
Key_source_Monthly_usage_fee_TAX = NullOrEmpty(row["Key_source_Monthly_usage_fee_TAX"].ToString());
Key_source_Monthly_usage_fee_INCLUDING_TAX = NullOrEmpty(row["Key_source_Monthly_usage_fee_INCLUDING_TAX"].ToString());
Supplier_Initial_expense_REQ_SEQ = NullOrEmpty(row["Supplier_Initial_expense_REQ_SEQ"].ToString());
Supplier_Initial_expense = NullOrEmpty(row["Supplier_Initial_expense"].ToString());
Supplier_Initial_expense_DISCOUNTED = NullOrEmpty(row["Supplier_Initial_expense_DISCOUNTED"].ToString());
Supplier_Initial_expense_TAX = NullOrEmpty(row["Supplier_Initial_expense_TAX"].ToString());
Supplier_Initial_expense_INCLUDING_TAX = NullOrEmpty(row["Supplier_Initial_expense_INCLUDING_TAX"].ToString());
Supplier_Monthly_usage_fee_REQ_SEQ = NullOrEmpty(row["Supplier_Monthly_usage_fee_REQ_SEQ"].ToString());
supplier_Monthly_usage_fee = NullOrEmpty(row["Supplier_Monthly_usage_fee"].ToString());
Supplier_Monthly_usage_fee_DISCOUNTED = NullOrEmpty(row["Supplier_Monthly_usage_fee_DISCOUNTED"].ToString());
Supplier_Monthly_usage_fee_TAX = NullOrEmpty(row["Supplier_Monthly_usage_fee_TAX"].ToString());
Supplier_Monthly_usage_fee_INCLUDING_TAX = NullOrEmpty(row["Supplier_Monthly_usage_fee_INCLUDING_TAX"].ToString());
Production_information_browsing_Initial_expense_REQ_SEQ = NullOrEmpty(row["Production_information_browsing_Initial_expense_REQ_SEQ"].ToString());
Production_information_browsing_Initial_expense = NullOrEmpty(row["Production_information_browsing_Initial_expense"].ToString());
Production_information_browsing_Initial_expense_DISCOUNTED = NullOrEmpty(row["Production_information_browsing_Initial_expense_DISCOUNTED"].ToString());
Production_information_browsing_Initial_expense_TAX = NullOrEmpty(row["Production_information_browsing_Initial_expense_TAX"].ToString());
Production_information_browsing_Initial_expense_INCLUDING_TAX = NullOrEmpty(row["Production_information_browsing_Initial_expense_INCLUDING_TAX"].ToString());
View_production_information_Annual_usage_fee_REQ_SEQ = NullOrEmpty(row["View_production_information_Annual_usage_fee_REQ_SEQ"].ToString());
Viewing_production_information_Annual_usage_fee = NullOrEmpty(row["Viewing_production_information_Annual_usage_fee"].ToString());
View_production_information_Annual_usage_fee_DISCOUNTED = NullOrEmpty(row["View_production_information_Annual_usage_fee_DISCOUNTED"].ToString());
View_production_information_Annual_usage_fee_TAX = NullOrEmpty(row["View_production_information_Annual_usage_fee_TAX"].ToString());
View_production_information_Annual_usage_fee_INCLUDING_TAX = NullOrEmpty(row["View_production_information_Annual_usage_fee_INCLUDING_TAX"].ToString());
decimal usageFee = Key_source_Monthly_usage_fee_REQ_SEQ + Key_source_Monthly_usage_fee + Key_source_Monthly_usage_fee_DISCOUNTED + Key_source_Monthly_usage_fee_TAX + Key_source_Monthly_usage_fee_INCLUDING_TAX;
newRow["KEY_SOURCE_MONTHLY_USAGE_FEE"] = usageFee;
decimal supplierInitialExpense = Supplier_Initial_expense_REQ_SEQ + Supplier_Initial_expense + Supplier_Initial_expense_DISCOUNTED + Supplier_Initial_expense_TAX + Supplier_Initial_expense_INCLUDING_TAX;
newRow["SUPPLIER_INITIAL_EXPENSE"] = supplierInitialExpense;
decimal supplierMonthlyUsageFee = Supplier_Monthly_usage_fee_REQ_SEQ + supplier_Monthly_usage_fee + Supplier_Monthly_usage_fee_DISCOUNTED + Supplier_Monthly_usage_fee_TAX + Supplier_Monthly_usage_fee_INCLUDING_TAX;
newRow["SUPPLIER_MONTHLY_USAGE_FEE"] = supplierMonthlyUsageFee;
decimal browsingInitialExpense = Production_information_browsing_Initial_expense_REQ_SEQ + Production_information_browsing_Initial_expense + Production_information_browsing_Initial_expense_DISCOUNTED + Production_information_browsing_Initial_expense_TAX + Production_information_browsing_Initial_expense_INCLUDING_TAX;
newRow["BROWSING_INITIAL_EXPENSE"] = browsingInitialExpense;
decimal yearlyUsageFee = View_production_information_Annual_usage_fee_REQ_SEQ + Viewing_production_information_Annual_usage_fee + View_production_information_Annual_usage_fee_DISCOUNTED + View_production_information_Annual_usage_fee_TAX + View_production_information_Annual_usage_fee_INCLUDING_TAX;
newRow["YEARLY_USAGE_FEE"] = yearlyUsageFee;
//newRow["INVOICE_METHOD"] = row["BILL_METHOD"];
string invoiceMethod = row["BILL_METHOD"].ToString();
if (invoiceMethod.Length == 5)
{
if (invoiceMethod.Substring(0, 1) == "1")
{
newRow["POSTAL_MAIL"] = "●";
}
if (invoiceMethod.Substring(1, 1) == "1")
{
newRow["WEB"] = "●";
}
if (invoiceMethod.Substring(2, 1) == "1")
{
newRow["Email"] = "●";
}
if (invoiceMethod.Substring(3, 1) == "1")
{
newRow["CREDIT_CARD"] = "●";
}
if (invoiceMethod.Substring(4, 1) == "1")
{
newRow["OTHER"] = "●";
}
}
dtTotal.Rows.Add(newRow);
#endregion
}
DataSet ds = new DataSet();
ds.Tables.Add(dtResult);
ds.Tables.Add(dtTotal);
response.Data = Utility.Utility_Component.DsToJSon(ds, "UsageApplicatioList");
if (dtResult.Rows.Count > 0)
{
response.Status = 1;
}
else
{
if (strMessage == "")
{
response.Status = 1;
response.Message = "There is no data to display.";
}
else
{
response.Status = 0;
response.Message = strMessage;
}
}
response.Meta.Offset = OFFSET;
response.Meta.Limit = LIMIT;
response.Meta.Total = TOTAL;
timer.Stop();
response.Meta.Duration = timer.Elapsed.TotalSeconds;
return(response);
}
catch (Exception ex)
{
return(ResponseUtility.GetUnexpectedResponse(response, timer, ex));
}
}
public MetaResponse CheckAlreadyCreated(string BILLING_DATE, string status)
{
DataTable result = new DataTable();
result.Clear();
result.Columns.Add("Count");
result.Columns.Add("Error Message");
int count = 0;
string checkStatus = "";
string strMsg = "";
INVOICE_INFO DAL_INVOICE_INFO = new INVOICE_INFO(con);
DateTime date = Convert.ToDateTime(BILLING_DATE);
String YEAR_MONTH = date.ToString("yyMM");
if (status.Trim() == "0")
{
count = DAL_INVOICE_INFO.IsAlreadyCreated(YEAR_MONTH, out strMsg);
}
//need to change logic
if (count == 0)
{
//Call Create InvoiceData Method
checkStatus = CreateInvoiceData(BILLING_DATE, status);
if (checkStatus == "3")
{
DataRow dr = result.NewRow();
dr["Count"] = 0;
dr["Error Message"] = Utility.Messages.Jimugo.E000WC001; //E000WC001
result.Rows.Add(dr);
response.Data = Utility.Utility_Component.DtToJSon(result, "Return Message");
}
if (checkStatus == "0") // Delete Fail
{
DataRow dr = result.NewRow();
dr["Count"] = 0;
dr["Error Message"] = Utility.Messages.Jimugo.E000WC003; //E000WC003
result.Rows.Add(dr);
response.Data = Utility.Utility_Component.DtToJSon(result, "Return Message");
}
else if (checkStatus == "2") //Insert Fail
{
DataRow dr = result.NewRow();
dr["Count"] = 0;
dr["Error Message"] = Utility.Messages.Jimugo.E000WC005; //E000WC005
result.Rows.Add(dr);
}
else if (checkStatus.Length > 2) //Insert Success
{
string[] authorsList = checkStatus.Split(',');
DataRow dr = result.NewRow();
dr["Count"] = 2;
dr["error message"] = string.Format(Utility.Messages.Jimugo.I000WC002, authorsList[1]); //i000wc002
result.Rows.Add(dr);
}
//return response;
}
else
{
DataRow dr = result.NewRow();
dr["Count"] = 1;
dr["Error Message"] = Utility.Messages.Jimugo.I000WC001; //I000WC001
result.Rows.Add(dr);
}
response.Data = Utility.Utility_Component.DtToJSon(result, "Return Message");
timer.Stop();
response.Meta.Duration = timer.Elapsed.TotalMilliseconds;
return(response);
}
public Response UpdateInvoice_37(string strData)
{
try
{
DataTable dt = Utility.Utility_Component.JsonToDt(strData);
string strError = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
string STATUS_CASH_FORECAST_MM = (dt.Rows[i]["STATUS_CASH_FORECAST_MM"] == null ? "" : dt.Rows[i]["STATUS_CASH_FORECAST_MM"].ToString());
string STATUS_PLAN_DEPOSIT_YYMM = (dt.Rows[i]["STATUS_PLAN_DEPOSIT_YYMM"] == null ? "" : dt.Rows[i]["STATUS_PLAN_DEPOSIT_YYMM"].ToString());
string STATUS_ACTUAL_MDB_UPDATE = (dt.Rows[i]["STATUS_ACTUAL_MDB_UPDATE"] == null ? "" : dt.Rows[i]["STATUS_ACTUAL_MDB_UPDATE"].ToString());
string STATUS_ACC_RECEIVABLE_DATE = (dt.Rows[i]["STATUS_ACC_RECEIVABLE_DATE"] == null ? "" : dt.Rows[i]["STATUS_ACC_RECEIVABLE_DATE"].ToString());
string DUNNING_DATE = (dt.Rows[i]["DUNNING_DATE"] == null ? null : dt.Rows[i]["DUNNING_DATE"].ToString());
string ANSWER_DATE = (dt.Rows[i]["ANSWER_DATE"] == null ? null : dt.Rows[i]["ANSWER_DATE"].ToString());
string PAYMENT_DUE_DATE = (dt.Rows[i]["PAYMENT_DUE_DATE"] == null ? null : dt.Rows[i]["PAYMENT_DUE_DATE"].ToString());
string ANSWER_MEMO = (dt.Rows[i]["ANSWER_MEMO"] == null ? "" : dt.Rows[i]["ANSWER_MEMO"].ToString());
string COMPANY_NO_BOX = dt.Rows[i]["COMPANY_NO_BOX"].ToString();
string YEAR_MONTH = dt.Rows[i]["YEAR_MONTH"].ToString();
int DUNNING_COUNT = int.Parse(dt.Rows[i]["DUNNING_COUNT"] == null ? "0" : dt.Rows[i]["DUNNING_COUNT"].ToString());
DateTime?dtm_DUNNING_DATE, dtm_ANSWER_DATE, dtm_PAYMENT_DUE_DATE, dtm_STATUS_ACC_RECEIVABLE_DATE, dtm_STATUS_ACTUAL_MDB_UPDATE;
try
{
dtm_DUNNING_DATE = DateTime.ParseExact(DUNNING_DATE, "yyyyMMdd", CultureInfo.InvariantCulture);
}
catch (Exception)
{
dtm_DUNNING_DATE = null;
}
try
{
dtm_ANSWER_DATE = DateTime.ParseExact(ANSWER_DATE, "yyyyMMdd", CultureInfo.InvariantCulture);
}
catch (Exception)
{
dtm_ANSWER_DATE = null;
}
try
{
dtm_PAYMENT_DUE_DATE = DateTime.ParseExact(PAYMENT_DUE_DATE, "yyyyMMdd", CultureInfo.InvariantCulture);
}
catch (Exception)
{
dtm_PAYMENT_DUE_DATE = null;
}
try
{
dtm_STATUS_ACTUAL_MDB_UPDATE = DateTime.ParseExact(STATUS_ACTUAL_MDB_UPDATE, "yyyyMMdd", CultureInfo.InvariantCulture);
}
catch (Exception)
{
dtm_STATUS_ACTUAL_MDB_UPDATE = null;
}
try
{
dtm_STATUS_ACC_RECEIVABLE_DATE = DateTime.ParseExact(STATUS_ACC_RECEIVABLE_DATE, "yyyyMMdd", CultureInfo.InvariantCulture);
}
catch (Exception)
{
dtm_STATUS_ACC_RECEIVABLE_DATE = null;
}
INVOICE_INFO oINV = new INVOICE_INFO(Properties.Settings.Default.MyConnection);
BOL_INVOICE_INFO oINFO = new BOL_INVOICE_INFO();
oINFO.STATUS_CASH_FORECAST_MM = STATUS_CASH_FORECAST_MM;
oINFO.STATUS_PLAN_DEPOSIT_YYMM = STATUS_PLAN_DEPOSIT_YYMM;
oINFO.STATUS_ACC_RECEIVABLE_DATE = dtm_STATUS_ACC_RECEIVABLE_DATE;
oINFO.STATUS_ACTUAL_MDB_UPDATE = dtm_STATUS_ACTUAL_MDB_UPDATE;
oINFO.DUNNING_DATE = dtm_DUNNING_DATE;
oINFO.ANSWER_DATE = dtm_ANSWER_DATE;
oINFO.ANSWER_MEMO = ANSWER_MEMO;
oINFO.PAYMENT_DUE_DATE = dtm_PAYMENT_DUE_DATE;
oINFO.COMPANY_NO_BOX = COMPANY_NO_BOX;
oINFO.YEAR_MONTH = YEAR_MONTH;
string strMessage = "";
if (!oINV.CheckIfItIsOrigin(oINFO.COMPANY_NO_BOX, oINFO.YEAR_MONTH, oINFO.DUNNING_DATE, out strMessage))
{
oINFO.DUNNING_COUNT = DUNNING_COUNT + 1;
}
else
{
oINFO.DUNNING_COUNT = DUNNING_COUNT;
}
oINV.UpdateInvoiceFor37(oINFO, out strMessage);
strError = strError + strMessage;
}
if (strError == "")
{
response.Status = 1;
response.Message = "Success.";
}
else
{
response.Status = 0;
response.Message = strError;
}
return(response);
}
catch (Exception ex)
{
response.Status = 0;
response.Message = ex.Message + "\n" + ex.StackTrace;
return(response);
}
}
public Response GetScheduled_Payment(
int TRANSACTION_TYPE,
string FROM_YEAR_MONTH,
string TO_YEAR_MONTH,
string FROM_STATUS_PLAN_DEPOSIT_YYMM,
string TO_STATUS_PLAN_DEPOSIT_YYMM,
string FROM_STATUS_ACTUAL_DEPOSIT_DATE,
string TO_STATUS_ACTUAL_DEPOSIT_DATE,
string DEPOSIT_RULE,
string WITH_OR_WITHOUT_PAYMENT,
string BILL_SUPPLIER_NAME)
{
try
{
INVOICE_INFO oINV = new INVOICE_INFO(Properties.Settings.Default.MyConnection);
string strMessage = "";
string from_yearMonth = Utility_Component.GetYearMonth(FROM_YEAR_MONTH, true);
string to_yearMonth = Utility_Component.GetYearMonth(TO_YEAR_MONTH, false);
string from_statusPlanYYMM = Utility_Component.GetYearMonth(FROM_STATUS_PLAN_DEPOSIT_YYMM, true);
string to_statusPlanYYMM = Utility_Component.GetYearMonth(TO_STATUS_PLAN_DEPOSIT_YYMM, false);
string from_statusPlanDate = Utility_Component.GetFullDate(FROM_STATUS_ACTUAL_DEPOSIT_DATE);
string to_statusPlanDate = Utility_Component.GetFullDate(TO_STATUS_ACTUAL_DEPOSIT_DATE);
DataTable dt = oINV.getDataForScheduled_Payment(
TRANSACTION_TYPE,
from_yearMonth, to_yearMonth,
from_statusPlanYYMM, to_statusPlanYYMM,
from_statusPlanDate, to_statusPlanDate,
DEPOSIT_RULE,
WITH_OR_WITHOUT_PAYMENT,
BILL_SUPPLIER_NAME,
out strMessage
);
response.Data = Utility.Utility_Component.DtToJSon(dt, "Scheduled Payment");
if (dt.Rows.Count > 0)
{
response.Status = 1;
response.Message = "Success";
}
else
{
if (strMessage == "")
{
response.Status = 1;
response.Message = "There has no data to display.";
}
else
{
response.Status = 0;
response.Message = strMessage;
}
}
return(response);
}
catch (Exception ex)
{
response.Status = 0;
response.Message = ex.Message + "\n" + ex.StackTrace;
return(response);
}
}
public Response CancelAllocation(string strData)
{
using (TransactionScope dbTxn = new TransactionScope())
{
try
{
string strReturnMsg = "";
string strMSG = "";
DataTable dtData = AmigoProcessManagement.Utility.Utility_Component.JsonToDt(strData);
if (dtData.Rows.Count > 0)
{
for (int i = 0; i < dtData.Rows.Count; i++)
{
int GRID_SEQ_NO = AmigoProcessManagement.Utility.Utility_Component.dtColumnToInt((dtData.Rows[i]["SEQ_NO"] == null ? "" : dtData.Rows[i]["SEQ_NO"].ToString()));
string GRID_COMPANY_NO_BOX = dtData.Rows[i]["COMPANY_NO_BOX"] == null ? "" : dtData.Rows[i]["COMPANY_NO_BOX"].ToString();
string GRID_YEAR_MONTH = dtData.Rows[i]["YEAR_MONTH"] == null ? "" : dtData.Rows[i]["YEAR_MONTH"].ToString();
string GRID_BILLING_CODE = GRID_COMPANY_NO_BOX + GRID_YEAR_MONTH;
//getAllocatedAmount
RESERVE_INFO oRI = new RESERVE_INFO(Properties.Settings.Default.MyConnection);
DataTable dt = oRI.GetReservceInfoByBillingCode(GRID_BILLING_CODE);
if (dt.Rows.Count > 0)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
//UpdateReceiptDetail
RECEIPT_DETAILS oRecep = new RECEIPT_DETAILS(Properties.Settings.Default.MyConnection);
int RESERVE_SEQ_NO = int.Parse(dt.Rows[j][0].ToString());
oRecep.CancelAllocation(RESERVE_SEQ_NO, out strMSG);
strReturnMsg = strMSG;
//GET COMPANY_NO_BOX from Reserve By SEQ_N0
DataTable BILLING_CODES = oRI.GetCompanyNoBoxBySEQNO(RESERVE_SEQ_NO, out strMSG);
for (int k = 0; k < BILLING_CODES.Rows.Count; k++)
{
string MATCHED_BILLING_CODE = BILLING_CODES.Rows[k][0].ToString();
//UpdateInvoiceInfo
INVOICE_INFO oINV = new INVOICE_INFO(Properties.Settings.Default.MyConnection);
oINV.UpdateInvoiceForCancel(MATCHED_BILLING_CODE.Substring(0, 10), MATCHED_BILLING_CODE.Substring(10, 5), out strMSG);
strReturnMsg = strReturnMsg + strMSG;
//DeleteReserveInfo
strMSG = oRI.removeReserve(RESERVE_SEQ_NO, MATCHED_BILLING_CODE);
strReturnMsg = strReturnMsg + strMSG;
}
}
}
if (strReturnMsg != "")
{
break;
}
}
}
else
{
strReturnMsg = "Data Error.";
}
if (strReturnMsg != "")
{
dbTxn.Dispose(); //rollback
response.Status = 0;
}
else
{
dbTxn.Complete(); //commit operation
response.Status = 1;
}
response.Message = strReturnMsg;
return(response);
}
catch (Exception ex)
{
response.Status = 0;
response.Message = ex.Message + "\n" + ex.StackTrace;
dbTxn.Dispose();
return(response);
}
}
}
