| Server IP : 103.6.199.200 / Your IP : 18.118.30.137 Web Server : Microsoft-IIS/10.0 System : Windows NT EMPUSA 10.0 build 20348 (Windows Server 2016) i586 User : EMPUSA$ ( 0) PHP Version : 7.4.33 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/Domains/iscommy2/ws.i-3s.com.my/i3sWebServiceStg/ |
Upload File : |
using System;
using System.Data;
using System.Collections.Generic;
using i3sWebService.Models;
using System.Web.Script.Serialization;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Threading.Tasks;
using System.Globalization;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using System.IO;
using System.Net.Mail;
using System.Net;
using iTextSharp.text.html.simpleparser;
using System.Text;
using System.Web.UI;
using System.Web;
namespace i3sWebService
{
public class MerchantClass
{
APIClass Api = new APIClass();
//public async Task<object> GetMerchantSettlementByDate(string DatePick, string MerchantId)
//{
// var value = string.Empty;
// string query = string.Empty;
// Sales sale = new Sales();
// string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
// var result = await Api.MerchantSettlement(DatePick, MerchantId);
// return sale;
//}
public object GetMerchantEmail(string SchoolId, string MerchantId)
{
//var value = string.Empty;
string strEmail = string.Empty;
Merchant merc = new Merchant();
List<MerchantProperty> dataList = new List<MerchantProperty>();
string query = "";
query += "SELECT EMAIL FROM tbl_merchants ";
query += "WHERE SCHOOLID = '" + SchoolId + "' AND ";
query += "MERCHANTID = '" + MerchantId + "' ";
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
try
{
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
while (dataReader.Read())
{
MerchantProperty prop = new MerchantProperty();
prop.Email = dataReader["EMAIL"].ToString(); ;
dataList.Add(prop);
}
merc.Status = "S";
merc.ListMerchant = dataList;
merc.Remarks = "Success";
}
else {
merc.Status = "F";
merc.Remarks = "Email not found! Please update your profile.";
}
}
catch (Exception ex)
{
merc.Status = "F";
merc.Remarks = ex.ToString();
}
}
return merc;
}
public object GetMerchantSalesByEmail(string SchoolId, string MerchantId, string TerminalId ,string strMonth, string Email)
{
var value = string.Empty;
string query = string.Empty;
string strEmail = string.Empty;
string strMercName = string.Empty;
string strMercId = string.Empty;
string strSerialNo = string.Empty;
string strCompany = string.Empty;
string strRegNo = string.Empty;
string strGstNo = string.Empty;
string strSchool = string.Empty;
bool success = false;
string message = string.Empty;
decimal TotAmt = 0;
decimal TotOK = 0;
decimal TotFailed = 0;
Sales sale = new Sales();
List<TerminalProperty> dataList = new List<TerminalProperty>();
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
query += "SELECT a.MERCHANTNAME, a.COMPANYNAME, a.EMAIL, a.WEBCASH_MERCHANTID, ";
query += "b.SERIALNO, a.COMPANYREGISTRATIONNO, a.GSTNO, c.SCHOOLNAME ";
query += "FROM tbl_merchants a LEFT JOIN ";
query += "tbl_terminal b ON b.MERCHANTID = a.MERCHANTID LEFT JOIN ";
query += "tbl_schools c ON c.SCHOOLID = b.SCHOOLID ";
query += "WHERE b.TERMINALID = "+ TerminalId + " AND a.MERCHANTID = "+ MerchantId + " AND a.SCHOOLID = " + SchoolId + "";
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
JavaScriptSerializer js = new JavaScriptSerializer();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
while (dataReader.Read())
{
strEmail = dataReader["EMAIL"].ToString();
strMercName = dataReader["MERCHANTNAME"].ToString();
strMercId = dataReader["WEBCASH_MERCHANTID"].ToString();
strSerialNo = dataReader["SERIALNO"].ToString();
strCompany = dataReader["COMPANYNAME"].ToString();
strRegNo = dataReader["COMPANYREGISTRATIONNO"].ToString();
strGstNo = dataReader["GSTNO"].ToString();
strSchool = dataReader["SCHOOLNAME"].ToString();
}
if (dataReader != null) dataReader.Close();
cmd.Dispose();
DateTime dt2 = DateTime.ParseExact(strMonth + "-01 12:00:00 AM", "yyyy-MM-dd hh:mm:ss tt", CultureInfo.InvariantCulture);
//string newdate = dt2.ToString("MMM yyyy");
var firstDayOfMonth = new DateTime(dt2.Year, dt2.Month, 1);
var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);
//var FirstDay = firstDayOfMonth.ToString("yyyy-MM-dd");
var LastDay = strMonth + "-" + lastDayOfMonth.ToString("dd");
query = "";
query += "SELECT * FROM V_TXNMASTER ";
query += "WHERE SCHOOLID = '" + SchoolId + "' AND ";
query += "WEBCASH_MERCHANTID = '" + strMercId + "' AND ";
query += "TERMINALID = '" + TerminalId + "' AND ";
query += "RECEIPTDATE BETWEEN '" + strMonth + "-01' AND '" + LastDay + "' ";
query += "ORDER BY RECEIPTDATE ";
MySqlCommand cmd2 = new MySqlCommand(query, connection);
MySqlDataReader dataReader2 = cmd2.ExecuteReader();
if (dataReader2.HasRows == true)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[5] {
new DataColumn("Receipt Date"),
new DataColumn("Reference No."),
new DataColumn("Status"),
new DataColumn("No. of Transactions"),
new DataColumn("Amount")
});
int i = 1;
while (dataReader2.Read())
{
dt.Rows.Add(dataReader2["RECEIPTDATE"],
dataReader2["REFNO"].ToString(),
dataReader2["WOL_REF"].ToString(),
dataReader2["TXN_COUNT"].ToString(),
dataReader2["AMOUNT"].ToString()
);
if (dataReader2["WOL_REF"].ToString() == "OK")
{
TotOK = (TotOK + Convert.ToDecimal(dataReader2["AMOUNT"].ToString()));
}
else {
TotFailed = (TotFailed + Convert.ToDecimal(dataReader2["AMOUNT"].ToString()));
}
TotAmt = (TotAmt + Convert.ToDecimal(dataReader2["AMOUNT"].ToString()));
i++;
}
message = SendPDFEmailSales(dt, Email, strMercName, strCompany,
strMercId, strRegNo, strGstNo, strSerialNo, strSchool, TotAmt.ToString(),
TotFailed.ToString(), TotOK.ToString(), strMonth);
if (message.Split('#')[0] == "S")
{
sale.Status = "S";
sale.Remarks = message.Split('#')[1];
}
else {
sale.Status = "F";
sale.Remarks = message.Split('#')[1];
}
dataReader2.Close();
connection.Close();
}
else {
sale.Status = "F";
sale.Remarks = "No record found.";
}
}
else
{
sale.Status = "F";
sale.Remarks = "Email not found! Please update and save your email address under edit profile settings.";
}
}
return sale;
}
public object GetMerchantSettlementByEmail(string SchoolId, string MerchantId, string strMonth, string Email)
{
var value = string.Empty;
string query = string.Empty;
string strEmail = string.Empty;
string strMercName = string.Empty;
string strMercId = string.Empty;
string strCompany = string.Empty;
string strRegNo = string.Empty;
string strGstNo = string.Empty;
string strSchool = string.Empty;
bool success = false;
string message = string.Empty;
decimal TotAmt = 0;
decimal TotFee = 0;
decimal TotNet = 0;
Sales sale = new Sales();
List<TerminalProperty> dataList = new List<TerminalProperty>();
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
query += "SELECT a.MERCHANTNAME, a.COMPANYNAME, a.EMAIL, a.WEBCASH_MERCHANTID, ";
query += "a.COMPANYREGISTRATIONNO, a.GSTNO, c.SCHOOLNAME ";
query += "FROM tbl_merchants a LEFT JOIN ";
query += "tbl_schools c ON c.SCHOOLID = a.SCHOOLID ";
query += "WHERE a.MERCHANTID = " + MerchantId + " AND a.SCHOOLID = " + SchoolId + "";
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
JavaScriptSerializer js = new JavaScriptSerializer();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
while (dataReader.Read())
{
strEmail = dataReader["EMAIL"].ToString();
strMercName = dataReader["MERCHANTNAME"].ToString();
strMercId = dataReader["WEBCASH_MERCHANTID"].ToString();
strCompany = dataReader["COMPANYNAME"].ToString();
strRegNo = dataReader["COMPANYREGISTRATIONNO"].ToString();
strGstNo = dataReader["GSTNO"].ToString();
strSchool = dataReader["SCHOOLNAME"].ToString();
}
if (dataReader != null) dataReader.Close();
cmd.Dispose();
DateTime dt2 = DateTime.ParseExact(strMonth + "-01 12:00:00 AM", "yyyy-MM-dd hh:mm:ss tt", CultureInfo.InvariantCulture);
//string newdate = dt2.ToString("MMM yyyy");
var firstDayOfMonth = new DateTime(dt2.Year, dt2.Month, 1);
var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);
//var FirstDay = firstDayOfMonth.ToString("yyyy-MM-dd");
var LastDay = strMonth + "-" + lastDayOfMonth.ToString("dd");
query = "";
query += "SELECT * FROM V_MERCHANT_SETTLEMENT ";
query += "WHERE SCHOOLID = '" + SchoolId + "' AND ";
query += "WEBCASH_MERCHANTID = '" + strMercId + "' AND ";
query += "PAYMENTDATE BETWEEN '" + strMonth + "-01' AND '" + LastDay + "' ";
query += "ORDER BY PAYMENTDATE ASC ";
MySqlCommand cmd2 = new MySqlCommand(query, connection);
MySqlDataReader dataReader2 = cmd2.ExecuteReader();
if (dataReader2.HasRows == true)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[5] {
new DataColumn("Payment Date"),
new DataColumn("Reference No."),
new DataColumn("Amount"),
new DataColumn("Fee"),
new DataColumn("Net Amount")
});
int i = 1;
while (dataReader2.Read())
{
dt.Rows.Add(dataReader2["PAYMENTDATE"],
dataReader2["REFERENCENO"].ToString(),
dataReader2["AMOUNT"].ToString(),
dataReader2["FEE"].ToString(),
dataReader2["NETAMOUNT"].ToString()
);
TotAmt = (TotAmt + Convert.ToDecimal(dataReader2["AMOUNT"].ToString()));
TotFee = (TotFee + Convert.ToDecimal(dataReader2["FEE"].ToString()));
TotNet = (TotNet + Convert.ToDecimal(dataReader2["NETAMOUNT"].ToString()));
i++;
}
message = SendPDFEmailSettlement(dt, Email, strMercName, strCompany,
strMercId, strRegNo, strGstNo, strSchool, TotAmt.ToString(),
TotFee.ToString(), TotNet.ToString(), strMonth);
if (message.Split('#')[0] == "S")
{
sale.Status = "S";
sale.Remarks = message.Split('#')[1];
}
else
{
sale.Status = "F";
sale.Remarks = message.Split('#')[1];
}
dataReader2.Close();
connection.Close();
}
else
{
sale.Status = "F";
sale.Remarks = "No record found.";
}
}
else
{
sale.Status = "F";
sale.Remarks = "Email not found! Please update and save your email address under edit profile settings.";
}
}
return sale;
}
private string SendPDFEmailSales(DataTable dt, string EmailTo, string MerchantName, string CompanyName,
string MercWCId, string RegNo, string GstNo, string SerialNo, string School, string TotAmt,
string TotFailed, string TotOK, string strMonth)
{
string val = string.Empty;
//bool success = false;
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter hw = new HtmlTextWriter(sw))
{
DateTime dt2 = DateTime.ParseExact(strMonth + "-01 12:00:00 AM", "yyyy-MM-dd hh:mm:ss tt", CultureInfo.InvariantCulture);
string newdate = dt2.ToString("MMM yyyy");
StringBuilder sb = new StringBuilder();
sb.Append("<table width='100%' cellspacing='0' cellpadding='2'>");
sb.Append("<tr><td align='right' style='background-color: #18B5F0' colspan='2'><img src='http://www.ws.i-3s.com.my/i3sWebService/Images/i3s_logo_full.png' height='50' width='100%' align='center'/></td></tr>");
sb.Append("<tr><td align='center' style='background-color: #18B5F0' colspan='2'><b>Terminal Sales Report for " + newdate + "</b></td></tr>");
sb.Append("<tr><td colspan = '3'></td></tr>");
sb.Append("<tr><td><font size='1'><b>Serial No: </b>");
sb.Append(SerialNo);
sb.Append("</font></td><td align='right'><font size='1'><b>Date Printed: </b>");
sb.Append(DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss tt"));
sb.Append("</font></td></tr>");
sb.Append("<tr><td colspan='3'><font size='1'><b>Company Name :</b> ");
sb.Append(CompanyName + " ("+ RegNo +")");
sb.Append("</font></td></tr><tr><td colspan='3'><font size='1'><b>Location: </b>");
sb.Append(School);
sb.Append("</font></td></tr>");
sb.Append("</table>");
sb.Append("<br />");
sb.Append("<table border = '1'>");
sb.Append("<tr>");
foreach (DataColumn column in dt.Columns)
{
sb.Append("<th style='background-color:#D20B0C;color:#aaaaaa'><font color='#000000' align='center' size='1' >");
sb.Append(column.ColumnName);
sb.Append("</font></th>");
}
sb.Append("</tr>");
foreach (DataRow row in dt.Rows)
{
sb.Append("<tr>");
foreach (DataColumn column in dt.Columns)
{
if (column.ColumnName == "Amount")
{
sb.Append("<td align='right'><font size='1'>");
sb.Append(row[column]);
sb.Append("</font></td>");
}
else //if ((column.ColumnName == "No. of Transactions")||(column.ColumnName == "Status"))
{
sb.Append("<td align='center'><font size='1'>");
sb.Append(row[column]);
sb.Append("</font></td>");
}
//else {
// sb.Append("<td><font size='1'>");
// sb.Append(row[column]);
// sb.Append("</font></td>");
//}
}
sb.Append("</tr>");
}
//footer
sb.Append("<tr colspan = '4'><td align='right'><font size='1'><b>Sub Total (RM) :</b></td>");
sb.Append("<td align='right'><font>" + TotAmt + "</font></td>");
sb.Append("</tr>");
//sb.Append("<tr colspan = '6'><td align='right'><font size='1'><b>Tax (RM) :</b></font></td>");
//sb.Append("<td align='right'><font>0.00</font></td>");
//sb.Append("</tr>");
sb.Append("<tr colspan = '4'><td align='right'><font size='1'><b>Failed (RM) :</b></td>");
sb.Append("<td align='right'><font>- " + TotFailed + "</font></td>");
sb.Append("</tr>");
sb.Append("<tr colspan = '4'><td align='right'><font size='1'><b>Net Total (RM) :</b></td>");
sb.Append("<td align='right'><font>" + TotOK + "</font></td>");
sb.Append("</tr>");
sb.Append("</table>");
StringReader sr = new StringReader(sb.ToString());
Document pdfDoc = new Document(PageSize.A4, 20f, 20f, 20f, 20f);
HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
using (MemoryStream memoryStream = new MemoryStream())
{
PdfWriter writer = PdfWriter.GetInstance(pdfDoc, memoryStream);
pdfDoc.Open();
htmlparser.Parse(sr);
pdfDoc.Close();
byte[] bytes = memoryStream.ToArray();
memoryStream.Close();
try
{
MailMessage mm = new MailMessage();
mm.From = new MailAddress("noreply@i-3s.com.my");
mm.To.Add(EmailTo);
mm.Subject = "i-3s Terminal Statement for "+ newdate + " (S/N: " + SerialNo + ")";
mm.Body = CreateSalesEmailBody(MerchantName, newdate);
mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "PT_" + SerialNo + "_" + DateTime.Now.ToString("MM_yyyy") + ".pdf"));
mm.IsBodyHtml = true;
SmtpClient SmtpServer = new SmtpClient("mail.i-3s.com.my");
SmtpServer.Port = 25;
SmtpServer.Credentials = new System.Net.NetworkCredential("noreply@i-3s.com.my", "n0rep|y");
//SmtpServer.EnableSsl = true;
//SmtpServer.DeliveryMethod = SmtpDeliveryMethod.Network;
//SmtpServer.UseDefaultCredentials = false;
ServicePointManager.Expect100Continue = false;
SmtpServer.Send(mm);
//success = true;
val = "S#Mail sent successfully.";
}
catch (Exception ex)
{
val = "F#" + ex.Message;
//success = false;
}
}
}
}
return val;
}
private string SendPDFEmailSettlement(DataTable dt, string EmailTo, string MerchantName, string CompanyName,
string MercWCId, string RegNo, string GstNo, string School, string TotAmt,
string TotFee, string TotNet, string strMonth)
{
string val = string.Empty;
//bool success = false;
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter hw = new HtmlTextWriter(sw))
{
DateTime dt2 = DateTime.ParseExact(strMonth + "-01 12:00:00 AM", "yyyy-MM-dd hh:mm:ss tt", CultureInfo.InvariantCulture);
string newdate = dt2.ToString("MMM yyyy");
StringBuilder sb = new StringBuilder();
sb.Append("<table width='100%' cellspacing='0' cellpadding='2'>");
sb.Append("<tr><td align='right' style='background-color: #18B5F0' colspan='2'><img src='http://www.ws.i-3s.com.my/i3sWebService/Images/i3s_logo_full.png' height='50' width='100%' align='center'/></td></tr>");
sb.Append("<tr><td align='center' style='background-color: #18B5F0' colspan='2'><b>Settlement Report for " + newdate + "</b></td></tr>");
sb.Append("<tr><td colspan = '3'></td></tr>");
sb.Append("<tr><td><font size='1'><b>Company Name : </b>");
sb.Append(CompanyName + " (" + RegNo + ")");
sb.Append("</font></td><td align='right'><font size='1'><b>Date Printed: </b>");
sb.Append(DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss tt"));
sb.Append("</font></td></tr>");
sb.Append("<tr><td colspan='3'><font size='1'><b>Location: </b>");
sb.Append(School);
sb.Append("</font></td></tr>");
sb.Append("</table>");
sb.Append("<br />");
sb.Append("<table border = '1'>");
sb.Append("<tr>");
foreach (DataColumn column in dt.Columns)
{
sb.Append("<th style='background-color:#D20B0C;color:#aaaaaa'><font color='#000000' align='center' size='1' >");
sb.Append(column.ColumnName);
sb.Append("</font></th>");
}
sb.Append("</tr>");
foreach (DataRow row in dt.Rows)
{
sb.Append("<tr>");
foreach (DataColumn column in dt.Columns)
{
if (column.ColumnName == "Net Amount")
{
sb.Append("<td align='right'><font size='1'>");
sb.Append(row[column]);
sb.Append("</font></td>");
}
else //if ((column.ColumnName == "No. of Transactions")||(column.ColumnName == "Status"))
{
sb.Append("<td align='center'><font size='1'>");
sb.Append(row[column]);
sb.Append("</font></td>");
}
//else {
// sb.Append("<td><font size='1'>");
// sb.Append(row[column]);
// sb.Append("</font></td>");
//}
}
sb.Append("</tr>");
}
//footer
//footer
sb.Append("<tr><td align='right' colspan = '2'><font size='1'><b>Total (RM) :</b></td>");
sb.Append("<td align='center'><font>" + TotAmt + "</font></td>");
sb.Append("<td align='center'><font>" + TotFee + "</font></td>");
sb.Append("<td align='right'><font>" + TotNet + "</font></td>");
sb.Append("</tr>");
sb.Append("</table>");
StringReader sr = new StringReader(sb.ToString());
Document pdfDoc = new Document(PageSize.A4, 20f, 20f, 20f, 20f);
HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
using (MemoryStream memoryStream = new MemoryStream())
{
PdfWriter writer = PdfWriter.GetInstance(pdfDoc, memoryStream);
pdfDoc.Open();
htmlparser.Parse(sr);
pdfDoc.Close();
byte[] bytes = memoryStream.ToArray();
memoryStream.Close();
try
{
MailMessage mm = new MailMessage();
mm.From = new MailAddress("noreply@i-3s.com.my");
mm.To.Add(EmailTo);
mm.Subject = "i-3s Settlement Statement for " + newdate;
mm.Body = CreateSettlementEmailBody(MerchantName, newdate);
mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "Settlement_Report_" + DateTime.Now.ToString("MM_yyyy") + ".pdf"));
mm.IsBodyHtml = true;
SmtpClient SmtpServer = new SmtpClient("mail.i-3s.com.my");
SmtpServer.Port = 25;
SmtpServer.Credentials = new System.Net.NetworkCredential("noreply@i-3s.com.my", "n0rep|y");
//SmtpServer.EnableSsl = true;
//SmtpServer.DeliveryMethod = SmtpDeliveryMethod.Network;
//SmtpServer.UseDefaultCredentials = false;
ServicePointManager.Expect100Continue = false;
SmtpServer.Send(mm);
//success = true;
val = "S#Mail sent successfully.";
}
catch (Exception ex)
{
val = "F#" + ex.Message;
//success = false;
}
}
}
}
return val;
}
private string CreateSalesEmailBody(string fullname, string month)
{
string body = string.Empty;
using (StreamReader reader = new StreamReader(HttpContext.Current.Server.MapPath("~/EmailTemplate/StatementEmail.html")))
{
body = reader.ReadToEnd();
}
body = body.Replace("{FullName}", fullname);
body = body.Replace("{Month}", month);
return body;
}
private string CreateSettlementEmailBody(string fullname, string month)
{
string body = string.Empty;
using (StreamReader reader = new StreamReader(HttpContext.Current.Server.MapPath("~/EmailTemplate/SettlementEmail.html")))
{
body = reader.ReadToEnd();
}
body = body.Replace("{FullName}", fullname);
body = body.Replace("{Month}", month);
return body;
}
private DataTable GetDataTable(string query)
{
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection conn = new MySqlConnection(strConnString))
{
MySqlCommand cmd = new MySqlCommand(query, conn);
using (MySqlDataAdapter sda = new MySqlDataAdapter())
{
cmd.Connection = conn;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt;
}
}
}
}
public object GetMerchantTerminalList(string SchoolId, string MerchantId)
{
var value = string.Empty;
var fdate = DateTime.Now.ToString("MM-yyyy");
var firstDayOfMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);
var ldate = lastDayOfMonth.ToString("dd-MM-yyyy");
string query = string.Empty;
string strTerminalId = string.Empty;
string strSerialNo = string.Empty;
string strModel = string.Empty;
string strIMEI = string.Empty;
string strStatus = string.Empty;
Terminal tmn = new Terminal();
List<TerminalProperty> dataList = new List<TerminalProperty>();
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
query += "SELECT a.TERMINALID,a.SERIALNO,a.MODEL,a.IMEI, b.DESCRIPTION STATUSDESC "; //SUM(d.TOTALAMOUNT) TOTALAMOUNT ";
query += "FROM tbl_terminal a LEFT JOIN ";
query += "tbl_lookupstatus b ON b.CODE = a.STATUSCODE AND b.TABLENAME = 'TERMINAL' ";
//query += "tbl_txnterminal c ON c.TERMINALID = a.TERMINALID AND c.MERCHANTID = a.MERCHANTID INNER JOIN ";
//query += "tbl_txnmaster d ON d.REFNO = c.REFNO ";
query += "WHERE a.SCHOOLID = '" + SchoolId + "' AND a.MERCHANTID = '" + MerchantId + "' AND b.DESCRIPTION = 'Active' ";
//query += "AND DATE_FORMAT(c.STARTOFJOB, '%d-%m-%Y %H:%i:%s') > '01-" + fdate + " 00:00:00' AND ";
//query += "DATE_FORMAT(c.ENDOFJOB, '%d-%m-%Y %H:%i:%s') < '" + ldate + " 23:59:59' ";
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
JavaScriptSerializer js = new JavaScriptSerializer();
MySqlDataReader dataReader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dataReader);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
//TerminalProperty prop = new TerminalProperty();
strTerminalId = row["TERMINALID"].ToString();
strSerialNo = row["SERIALNO"].ToString();
strModel = row["MODEL"].ToString();
strIMEI = row["IMEI"].ToString();
strStatus = row["STATUSDESC"].ToString();
//prop.TotalAmount = dataReader["TOTALAMOUNT"].ToString();
//dataList.Add(prop);
query = "";
query += "SELECT STARTOFJOB, CASE WHEN ENDOFJOB='0000-00-00 00:00:00' THEN NULL ELSE ENDOFJOB END ENDOFJOB ";
query += "FROM tbl_txnterminal WHERE TERMINALID = '" + strTerminalId + "' ";
query += "ORDER BY STARTOFJOB DESC LIMIT 1";
//close Data Reader
if (dataReader != null) dataReader.Close();
MySqlCommand cmd2 = new MySqlCommand(query, connection);
MySqlDataReader dataReader2 = cmd2.ExecuteReader();
if (dataReader2.HasRows == true)
{
while (dataReader2.Read())
{
TerminalProperty prop = new TerminalProperty();
prop.TerminalId = strTerminalId;
prop.SerialNo = strSerialNo;
prop.Model = strModel;
prop.IMEI = strIMEI;
prop.StatusDesc = strStatus;
if (string.IsNullOrEmpty(dataReader2["ENDOFJOB"].ToString()))
{
prop.Online = "Y";
}
else
{
prop.Online = "N";
}
dataList.Add(prop);
}
}
else
{
TerminalProperty prop = new TerminalProperty();
prop.TerminalId = strTerminalId;
prop.SerialNo = strSerialNo;
prop.Model = strModel;
prop.IMEI = strIMEI;
prop.StatusDesc = strStatus;
prop.Online = "N";
dataList.Add(prop);
}
if (dataReader2 != null) dataReader2.Close();
}
tmn.Status = "S";
tmn.ListTerminal = dataList;
tmn.Remarks = "OK";
}
else
{
tmn.Status = "F";
tmn.Remarks = "No record found!";
}
//close Connection
connection.Close();
return tmn;
}
}
public object GetTerminalSalesHistory(string SchoolId, string MerchantId, string TerminalId, string Range, string Month, string Year)
{
var value = string.Empty;
string query = string.Empty;
var today = DateTime.Now.ToString("dd-MM-yyyy");
Sales sls = new Sales();
List<SalesProperty> dataList = new List<SalesProperty>();
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
if (Range == "Today")
{
query += "SELECT b.CARDNO, b.TOTALAMOUNT, b.REFNO,";
query += "DATE_FORMAT(b.RECEIPTDATE,'%d-%m-%Y %h:%m:%S %p') RECEIPTDATE ";
query += "FROM tbl_txnterminal a LEFT JOIN ";
query += "tbl_txnmaster b ON b.REFNO = a.REFNO ";
query += "WHERE a.SCHOOLID = '" + SchoolId + "' AND a.MERCHANTID = '" + MerchantId + "' AND ";
query += "a.TERMINALID = '" + TerminalId + "' AND b.STATUSCODE = '1' AND ";
query += "DATE_FORMAT(b.RECEIPTDATE,'%d-%m-%Y') like '%" + today + "%' ";
query += "ORDER BY b.RECEIPTDATE DESC ";
}
else if (Range == "Week")
{
int thisWeekNumber = GetIso8601WeekOfYear(DateTime.Today);
DateTime firstDayOfWeek = FirstDateOfWeek(DateTime.Now.Year, thisWeekNumber, CultureInfo.CurrentCulture);
DateTime LastDayOfWeek = firstDayOfWeek.AddDays(6);
var FirstDay = firstDayOfWeek.ToString("yyyy-MM-dd");
var LastDay = LastDayOfWeek.ToString("yyyy-MM-dd");
query += "SELECT DATE_FORMAT(a.RECEIPTDATE,'%d-%m-%Y') RECEIPTDATE, ";
query += "COUNT(a.REFNO) TXN_COUNT, a.REFNO, SUM(a.TOTALAMOUNT) TOTALAMOUNT ";
query += "FROM tbl_txnmaster a LEFT JOIN ";
query += "tbl_txnterminal b ON b.REFNO = a.REFNO ";
query += "WHERE b.SCHOOLID = '" + SchoolId + "' AND b.MERCHANTID = '" + MerchantId + "' AND ";
query += "b.TERMINALID = '" + TerminalId + "' AND a.STATUSCODE = '1' AND ";
query += "a.RECEIPTDATE BETWEEN '" + FirstDay + " 00:00:00' AND '" + LastDay + " 23:59:59' ";
query += "GROUP BY DATE_FORMAT(a.RECEIPTDATE, '%d-%m-%Y') ";
query += "ORDER BY a.RECEIPTDATE DESC ";
}
else if (Range == "Month")
{
var firstDayOfMonth = new DateTime(Convert.ToInt16(Year), Convert.ToInt16(Month), 1);
var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);
var FirstDay = firstDayOfMonth.ToString("yyyy-MM-dd");
var LastDay = lastDayOfMonth.ToString("yyyy-MM-dd");
query += "SELECT DATE_FORMAT(a.RECEIPTDATE,'%d-%m-%Y') RECEIPTDATE, ";
query += "COUNT(a.REFNO) TXN_COUNT, a.REFNO, SUM(a.TOTALAMOUNT) TOTALAMOUNT ";
query += "FROM tbl_txnmaster a LEFT JOIN ";
query += "tbl_txnterminal b ON b.REFNO = a.REFNO ";
query += "WHERE b.SCHOOLID = '" + SchoolId + "' AND b.MERCHANTID = '" + MerchantId + "' AND ";
query += "b.TERMINALID = '" + TerminalId + "' AND a.STATUSCODE = '1' AND ";
query += "a.RECEIPTDATE BETWEEN '" + FirstDay + " 00:00:00' AND '" + LastDay + " 23:59:59' ";
query += "GROUP BY DATE_FORMAT(a.RECEIPTDATE, '%d-%m-%Y') ";
query += "ORDER BY a.RECEIPTDATE DESC ";
}
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
JavaScriptSerializer js = new JavaScriptSerializer();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
while (dataReader.Read())
{
SalesProperty prop = new SalesProperty();
if (Range == "Today")
{
prop.CardNo = dataReader["CARDNO"].ToString();
prop.RefNo = dataReader["REFNO"].ToString();
}
else {
prop.RefNo = dataReader["REFNO"].ToString();
prop.TxnCount = dataReader["TXN_COUNT"].ToString();
}
prop.TotalAmount = dataReader["TOTALAMOUNT"].ToString();
prop.ReceiptDate = dataReader["RECEIPTDATE"].ToString();
//prop.MasterId = dataReader["MASTERID"].ToString();
//prop.SchoolId = dataReader["SCHOOLID"].ToString();
//prop.MerchantId = dataReader["MERCHANTID"].ToString();
//prop.CashierId = dataReader["CASHIERID"].ToString();
//prop.TerminalId = dataReader["TERMINALID"].ToString();
//prop.GSTAmount = dataReader["GSTAMOUNT"].ToString();
//prop.DiscountAmount = dataReader["DISCOUNTAMOUNT"].ToString();
dataList.Add(prop);
}
sls.Status = "S";
sls.ListSales = dataList;
sls.Remarks = "OK";
}
else
{
sls.Status = "F";
sls.Remarks = "No record found!";
}
//close Data Reader
dataReader.Close();
//close Connection
connection.Close();
return sls;
}
}
public static int GetIso8601WeekOfYear(DateTime time)
{
DayOfWeek day = CultureInfo.InvariantCulture.Calendar.GetDayOfWeek(time);
if (day >= DayOfWeek.Monday && day <= DayOfWeek.Wednesday)
{
time = time.AddDays(3);
}
return CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(time, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
}
public static DateTime FirstDateOfWeek(int year, int weekOfYear, System.Globalization.CultureInfo ci)
{
DateTime jan1 = new DateTime(year, 1, 1);
int daysOffset = (int)ci.DateTimeFormat.FirstDayOfWeek - (int)jan1.DayOfWeek;
DateTime firstWeekDay = jan1.AddDays(daysOffset);
int firstWeek = ci.Calendar.GetWeekOfYear(jan1, ci.DateTimeFormat.CalendarWeekRule, ci.DateTimeFormat.FirstDayOfWeek);
if ((firstWeek <= 1 || firstWeek >= 52) && daysOffset >= -3)
{
weekOfYear -= 1;
}
return firstWeekDay.AddDays(weekOfYear * 7);
}
public object GetStockDetailList(string SchoolId, string MerchantId, string StockId)
{
var value = string.Empty;
string query = string.Empty;
Stock stk = new Stock();
List<StockProperty> dataList = new List<StockProperty>();
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
query += "SELECT a.ID, d.SCHOOLID, d.MERCHANTID, b.CATEGORY, c.DESCRIPTION PRODUCT, a.UNITPRICE, a.QTY, a.OUT, a.BALANCE ";
query += "FROM tbl_stockdetail a LEFT JOIN ";
query += "tbl_productcategories b ON b.ID = a.CATID LEFT JOIN ";
query += "tbl_products c ON c.ID = a.PRODUCTID LEFT JOIN ";
query += "tbl_stockmaster d ON d.ID = a.STOCKID ";
query += "WHERE a.STOCKID = '"+ StockId + "' AND d.SCHOOLID='"+ SchoolId + "' AND d.MERCHANTID='" + MerchantId + "' ";
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
JavaScriptSerializer js = new JavaScriptSerializer();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
while (dataReader.Read())
{
StockProperty prop = new StockProperty();
prop.ItemId = dataReader["ID"].ToString();
prop.SchoolId = dataReader["SCHOOLID"].ToString();
prop.MerchantId = dataReader["MERCHANTID"].ToString();
prop.Code = dataReader["CATEGORY"].ToString();
prop.Item = dataReader["PRODUCT"].ToString();
prop.UnitPrice = dataReader["UNITPRICE"].ToString();
prop.Qty = dataReader["QTY"].ToString();
prop.Out = dataReader["OUT"].ToString();
prop.Balance = dataReader["BALANCE"].ToString();
dataList.Add(prop);
}
stk.Status = "S";
stk.ListStock = dataList;
stk.Remarks = "OK";
}
else
{
stk.Status = "F";
stk.Remarks = "No record found!";
}
//close Data Reader
dataReader.Close();
//close Connection
connection.Close();
return stk;
}
}
public object GetStockSearchList(string SchoolId, string MerchantId, string Search)
{
var value = string.Empty;
string query = string.Empty;
Stock stk = new Stock();
List<StockProperty> dataList = new List<StockProperty>();
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
query += "SELECT ID STOCKID, SCHOOLID, MERCHANTID, INVOICENO, INVOICEDATE, SUPPLIERCOMPANYNAME, SUPPLIERCONTACTPERSON, ";
query += "INVOICEAMOUNT, GSTAMOUNT, DISCOUNTAMOUNT, TOTALAMOUNT FROM tbl_stockmaster WHERE SCHOOLID = '" + SchoolId +"' AND MERCHANTID = '"+ MerchantId +"' ";
query += "AND(INVOICENO LIKE '%" + Search + "%' OR SUPPLIERCOMPANYNAME LIKE '%" + Search + "%' OR SUPPLIERCONTACTPERSON LIKE '%" + Search + "%') ";
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
JavaScriptSerializer js = new JavaScriptSerializer();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
while (dataReader.Read())
{
StockProperty prop = new StockProperty();
prop.StockId = dataReader["STOCKID"].ToString();
prop.SchoolId = dataReader["SCHOOLID"].ToString();
prop.MerchantId = dataReader["MERCHANTID"].ToString();
prop.InvoiceNo = dataReader["INVOICENO"].ToString();
prop.InvoiceDate = dataReader["INVOICEDATE"].ToString();
prop.SupplierCompanyName = dataReader["SUPPLIERCOMPANYNAME"].ToString();
prop.SupplierContactPerson = dataReader["SUPPLIERCONTACTPERSON"].ToString();
prop.InvoiceAmount = dataReader["INVOICEAMOUNT"].ToString();
prop.GSTAmount = dataReader["GSTAMOUNT"].ToString();
prop.DiscountAmount = dataReader["DISCOUNTAMOUNT"].ToString();
prop.TotalAmount = dataReader["TOTALAMOUNT"].ToString();
dataList.Add(prop);
}
stk.Status = "S";
stk.ListStock = dataList;
stk.Remarks = "OK";
}
else
{
stk.Status = "F";
stk.Remarks = "No record found!";
}
//close Data Reader
dataReader.Close();
//close Connection
connection.Close();
return stk;
}
}
}
}