private ObservableCollection <PrintKot> GetPrintKotForSave(KotMain kmO, string user)
{
ObservableCollection <PrintKot> pklist = new ObservableCollection <PrintKot>();
foreach (var kp in kmO.KotProdList)
{
PrintKot pk = new PrintKot();
pk.TABLENO = kp.TABLENO;
pk.DESCA = kp.ItemDesc;
pk.MENUCODE = getMenuCode(kp.MCODE);
pk.ISBOT = kp.ISBOT;
pk.QUANTITY = kp.Quantity;
pk.REMARKS = kp.Remarks;
pk.SNO = kp.SNO;
pk.UNIT = kp.UNIT;
pk.MCODE = kp.MCODE;
pk.TRNDATE = kp.TRNDATE == null?DateTime.Today:kp.TRNDATE.Value;
pk.REFSNO = kp.REFSNO;
pk.KOTID = kmO.KOTID;
//DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
//dtfi.ShortTimePattern = "hh:mm:ss tt";
//dtfi.TimeSeparator = ":";
//DateTime objDate = Convert.ToDateTime(kp.KOTTIME, dtfi);
// pk.KOTTIME = objDate;
pk.KOTTIME = GlobalClass.ServerDate;
pk.KOT = kp.KOT;
pk.USERNAME = user;
//pk.ComboItem = kp.ComboItemDesc;
//pk.PAX = kmO.Pax;
pklist.Add(pk);
}
return(pklist);
}
//private void ExecutemergeTableCommand(object obj)
//{
// try
// {
// string InsertQueryForKotProd = "INSERT INTO RMD_KOTPROD(KOTID,TABLENO,TRNDATE,MCODE,UNIT,Quantity,RealQty,AltQty,RATE,AMOUNT,DISCOUNT,VAT,REALRATE,IDIS,ALTUNIT,WAREHOUSE,KOT,SNO,ItemDesc,DIVISION,SERVICETAX,NAMNT,KOTTIME,KitchenDispatch,DispatchTime,DispatchUser,Remarks,ISBOT,ComboItem,ComboItemQty, WaiterName, REFSNO,RECEIPES,TAKEAWAY) VALUES(@KOTID,@TABLENO,@TRNDATE,@MCODE,@UNIT,@Quantity,@RealQty,@AltQty,@RATE,@AMOUNT,@DISCOUNT,@VAT,@REALRATE,@IDIS,@ALTUNIT,@WAREHOUSE,@KOT,@SNO,@ItemDesc,@DIVISION,@SERVICETAX,@NAMNT,@KOTTIME,@KitchenDispatch,@DispatchTime,@DispatchUser,@Remarks,@ISBOT,@ComboItem,@ComboItemQty, @WaiterName, @REFSNO,@RECEIPES,@TAKEAWAY)";
// var list = (popup.DataContext as MergeTableViewModel).TableListForMerge;
// List<KOTProd> kpMergedListForSave = new List<KOTProd>();
// using (SqlConnection con = new SqlConnection(ConnectionDbInfo.ConnectionString))
// {
// con.Open();
// SqlTransaction tran = con.BeginTransaction();
// try
// {
// foreach (var o in list.Where(x => x.IsTableChecked))
// {
// var kotprodLis = new List<KOTProd>(con.Query<KOTProd>("select kp.* from rmd_kotprod kp join rmd_kotmain_status kms on kp.kotid=kms.kotid where kms.status='ACTIVE' AND kp.TableNo='" + o.TableNo + "'", null, tran));
// if (kotprodLis.Count() == 0) { tran.Rollback(); MessageBox.Show("Error on merging Table.Sorry could not find KOT Items"); return; }
// kotprodLis.ForEach(x => x.Remarks = "MERGE FROM KOTID:" + x.KOTID);
// kpMergedListForSave.AddRange(kotprodLis);
// con.Execute("UPDATE RMD_KOTMAIN_STATUS SET STATUS='MERGE',REMARKS='MERGE TO: " + KotMainObj.KOTID + "' WHERE KOTID=" + kotprodLis.FirstOrDefault().KOTID, transaction: tran);
// }
// foreach (var t in kpMergedListForSave)
// {
// t.TABLENO = TableId;
// t.KOTID = KotMainObj.KOTID;
// }
// con.Execute(InsertQueryForKotProd, kpMergedListForSave, tran);
// tran.Commit(); //MessageBox.Show("Table Merging Successfull...");
// // DialogResult = true;
// }
// catch (Exception ex) {
// //tran.Rollback(); MessageBox.Show("Error on merging table..." + ex);
// }
// }
// }
// }
// }
// catch (Exception ex) { MessageBox.Show("Error Message..." + ex); }
//}
public string MergeTable(MergeTransfer mergeTransfer)
{
try
{
string DestinationTableNo = mergeTransfer.DestinationTableNo;
var list = mergeTransfer.MergingTables;
string InsertQueryForKotProd = "INSERT INTO RMD_KOTPROD(KOTID,TABLENO,TRNDATE,MCODE,UNIT,Quantity,RealQty,AltQty,RATE,AMOUNT,DISCOUNT,VAT,REALRATE,IDIS,ALTUNIT,WAREHOUSE,KOT,SNO,ItemDesc,DIVISION,SERVICETAX,NAMNT,KOTTIME,KitchenDispatch,DispatchTime,DispatchUser,Remarks,ISBOT,ComboItem,ComboItemQty, WaiterName, REFSNO,RECEIPES,TAKEAWAY) VALUES(@KOTID,@TABLENO,@TRNDATE,@MCODE,@UNIT,@Quantity,@RealQty,@AltQty,@RATE,@AMOUNT,@DISCOUNT,@VAT,@REALRATE,@IDIS,@ALTUNIT,@WAREHOUSE,@KOT,@SNO,@ItemDesc,@DIVISION,@SERVICETAX,@NAMNT,@KOTTIME,@KitchenDispatch,@DispatchTime,@DispatchUser,@Remarks,@ISBOT,@ComboItem,@ComboItemQty, @WaiterName, @REFSNO,@RECEIPES,@TAKEAWAY)";
List <KOTProd> kpMergedListForSave = new List <KOTProd>();
KotMain KotMainObj = new KotMain();
using (SqlConnection con = new SqlConnection(ConnectionDbInfo.ConnectionString))
{
con.Open();
SqlTransaction tran = con.BeginTransaction();
try
{
KotMainObj = con.Query <KotMain>("Select * from RMD_KOTMAIN kp JOIN RMD_KOTMAIN_STATUS KMS ON KP.KOTID=KMS.KOTID where KMS.STATUS='ACTIVE' and kp.TableNo='" + DestinationTableNo + "'", transaction: tran).FirstOrDefault();
foreach (var o in list)
{
var kotprodLis = new List <KOTProd>(con.Query <KOTProd>("select kp.* from rmd_kotprod kp join rmd_kotmain_status kms on kp.kotid=kms.kotid where kms.status='ACTIVE' AND kp.TableNo='" + o + "'", transaction: tran));
if (kotprodLis.Count() == 0)
{
tran.Rollback(); return("Error on merging Table.Sorry could not find KOT Items");
}
kotprodLis.ForEach(x => x.Remarks = "MERGE FROM KOTID:" + x.KOTID);
kpMergedListForSave.AddRange(kotprodLis);
con.Execute("UPDATE RMD_KOTMAIN_STATUS SET STATUS='MERGE',REMARKS='MERGE TO: " + KotMainObj.KOTID + "' WHERE KOTID=" + kotprodLis.FirstOrDefault().KOTID, transaction: tran);
}
foreach (var t in kpMergedListForSave)
{
t.TABLENO = DestinationTableNo;
t.KOTID = KotMainObj.KOTID;
}
con.Execute(InsertQueryForKotProd, kpMergedListForSave, transaction: tran);
tran.Commit();
return("success");
}
catch (Exception ex)
{
return("Error on merging table..." + ex.Message);
}
}
}
catch (Exception ex)
{
return(ex.Message);
}
}
public string CancelOrder(string tableNo, string user, string remarks)
{
try
{
string cancelRemarks = remarks;
using (SqlConnection con = new SqlConnection(ConnectionDbInfo.ConnectionString))
{
var Q = string.Format
(
@"SELECT MCODE, ItemDesc DESCA, ISNULL(SUM(Quantity),0) QUANTITY, MAX(KOTTIME) KOTTIME, MAX(UNIT) UNIT,
CASE WHEN K.Remarks='' THEN 'No Remarks' ELSE K.REMARKS END AS REMARKS,
CAST(ISBOT AS VARCHAR) IsBarItem, CAST(SNO AS VARCHAR) SNO,ISNULL(MAX(REFSNO),0) REFSNO, MAX(KOT) KOT FROM RMD_KOTPROD K JOIN RMD_KOTMAIN_STATUS KMS ON KMS.KOTID=K.KOTID WHERE KMS.STATUS='ACTIVE' AND K.TABLENO ='{0}'
GROUP BY MCODE, ItemDesc, K.Remarks, ISBOT, SNo", tableNo
);
var list = con.Query <KOTProd>(Q).ToList();
ObservableCollection <KOTProd> tempList = new ObservableCollection <KOTProd>();
var KOTID = con.ExecuteScalar <int>("SELECT KOTID FROM RMD_KOTMAIN_STATUS WHERE STATUS = 'ACTIVE' AND TABLENO = '" + tableNo + "'");
byte ps = con.ExecuteScalar <byte>("SELECT PREBILL_STATUS FROM RMD_KOTMAIN_STATUS WHERE KOTID=" + KOTID);
string RPN = con.ExecuteScalar <string>("SELECT REF_PREBILL_NOS FROM RMD_KOTMAIN_STATUS WHERE KOTID=" + KOTID);
if (ps == 1)
{
return("Pre Bill Detected...");
}
else if (!string.IsNullOrEmpty(RPN))
{
return("Pre Bill Detected...");
}
else
{
string role = con.ExecuteScalar <string>("select role from userProfiles where uname='" + user + "'");
byte PrebillStatus = con.ExecuteScalar <byte>("SELECT ISNULL(MIN(STATUS), 128) FROM RMD_KOTMAIN_PREBILL WHERE TABLENO = '" + tableNo + "' AND KOTID = " + KOTID);
if (role == "A" && PrebillStatus == 128)
{
foreach (var l in list.ToList())
{
if (tempList.FirstOrDefault(x => x.MCODE == l.MCODE) == null)
{
var SList = list.Where(w => w.MCODE == l.MCODE);
var sumQty = SList.Sum(x => x.Quantity);
if (sumQty > 0)
{
l.Quantity = -sumQty;
tempList.Add(l);
}
}
}
con.Open();
SqlTransaction tran = con.BeginTransaction();
try
{
var kpO = con.Query <KotMain>("select KM.* from rmd_kotmain KM JOIN RMD_KOTMAIN_STATUS KMS ON KM.KOTID=KMS.KOTID where KM.KOTID=" + KOTID, null, tran).SingleOrDefault();
KotMain km = new KotMain();
km = kpO;
km.KotProdList = tempList;
con.Execute(InsertQueryForKotPrint, GetPrintKotForSave(km, user), tran);
con.Execute("UPDATE RMD_KOTMAIN_STATUS SET STATUS='CANCELED', REMARKS='" + cancelRemarks + "' WHERE KOTID=" + KOTID, transaction: tran);
tran.Commit();
return("success");
}
catch (Exception ex) { tran.Rollback(); return("Can't cancel the table..." + ex); }
}
else
{
return("User doesnot have access or PreBill status is running.");
}
}
}
}
catch (Exception ex)
{
return(ex.Message);
}
}
